VLookup - reverse wild cards, strange results, reformatting advice

[Glaukopisignari]

Dear all,

I am reformatting transcripts, and I need a look-up table to return each speaker's participant group - eg facilitator, scientist, NGO worker. In the transcripts, the speakers are often introduced with a descriptor, then an acronym is used, so the first time the person speaks it has, for example, <WomanRedShorts (WRS):>. From then on, only WRS is used.

I have watched some videos on using wildcards, and I have put in the formula
=IF(ISNA(VLOOKUP("*"&B268&"*",participant!$A$2:$B$40,2)),"not found", VLOOKUP("*"&B268&"*", participant!$A$2:$B$40,2))
However, it returns 'not found', even though an exact match is in the look up table.

When I leave out the asterix and ampersand, it returns the right results.

in addition, the results for other entries don't correspond with the lookup table - UnknownM should return 'check', but is returning 'student' for example.

I am using Excel2008 for Mac.

I feel quite sure it's probably something simple, but I've been chasing my tail for hours and would be so grateful for some tips.

I've attached a sample. Not all the speakers' codes are in the look-up table, as I was trying to get the formula/formatting right first.

Thanks very much

Glaukopisignari.PGRG1_C1T3_forForum.xlsx

[Pete_UK]

There is a 4th (optional) parameter that can be used with VLOOKUP which controls whether you are looking for an exact match or the highest match which is less than the lookup value. This latter is the default setting if it is omitted, and so you need to include it in this case - it takes the value TRUE or FALSE (or 1 or 0, which is easier to type), so your formula should be:

=IF(ISNA(VLOOKUP("*"&B268&"*",participant!$A$2:$B$40,2,0)),"not found", VLOOKUP("*"&B268&"*", participant!$A$2:$B$40,2,0))

Hope this helps.

Pete

[Glaukopisignari]

Hi Pete - yes, thank you! Very grateful, G.