Vlookup in Excel

Resolved · Low Priority · Version 2007

Wed 28 Jul 2010, 11:09 Delegate Ginger said...

Ginger has attended:
Upgrade to Office 2007 course

Vlookup in Excel

Is there a way to vlookup for phrases that contain the target word, rather than just the exact word match; the "true" option is too general for this; I run into this problem often for example with country or company lists that are written with slightly different names in the two sheets.

Thu 29 Jul 2010, 11:46 Trainer Anthony said...

RE: vlookup in Excel

Hi Ginger, thanks for your query. Concatenate wildcards onto either side of the lookup value:

=VLOOKUP("*"&B19&"*", A1:C15, 3, FALSE)

That should find the lookup value even if it is in the middle of another string.

Hope this helps,

Anthony

Thu 29 Jul 2010, 14:31 Delegate Ginger said...

RE: vlookup in Excel

Thanks! Btw, is there a keyboard shortcut for these wildcards, as there is with the dollar signs?

Also, is there any way to pick up matches that are acronyms which don't actually contain the full word?

Thu 29 Jul 2010, 15:14 Trainer Anthony said...

RE: vlookup in Excel

Hi Ginger. The wildcards don't have an associated shortcut, unlike the dollar signs in formulas, you have to type the relevant wildcard into the formula as written. As for acronyms, you could potentially look for parts of the lookup value in the table array but that would involve an additional step to chop up the lookup value according to your specific criteria. Also, since it is an acronym, you would potentially be looking for just one letter which would be a minefield in a text field!

Hope this helps,

Anthony

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Microsoft Office tip:

Short cut tips

Instead of just seeing the default setting of screentip names, when you place the mouse pointer over the toolbar, you can also see the keyboard short cut for accessing the button as well.

To this click on Tools, Customise, options tab.

Under Other you will see the option of Show shortkeys in screentip

View all Microsoft Office hints and tips