VLookup wild cards [SOLVED]

[Chart68]

Hi,


Im trying to lookup the contents of a cell but I know the contents of the cell will not be an exact match, is there anyway I can use a wildcard or has someone got a better way of doing this


My formula is =VLOOKUP(C4,SM_CC!B:C,2,FALSE) C4 holds the text Pinewood but the column Im looking for holds Pinewood Avenue, so as its not an exact match Im returning a N/A.


Ive been trying to fix this for ages but cannot find a way of doing this so would be extremely grateful for a solution.


Kind Regards


Claire

 

[jason]

have you tried "TRUE" instead of "FALSE"? ('luck of the draw' results, but just curious...


i thought ive seen something like this before... will try and find it. will post if i do.

 

[NARAYANK991]

Hi Claire ,


Try this :


=VLOOKUP("*"&C4&"*",SM_CC!B:C,2,FALSE)


Narayan

 

[Chart68]

Hi everyone


thanks for your posts sorry I haven't got back to you sooner.


Narayan, thank you this helps narrow down the data Im looking for but I still get some N/As. But its a lot better than it was before


Claire

 

[Absolution]

Clair,


For your example above, the solution provided by NARAYANK991 will work perfectly.


Note that the lookup reference MUST be somewhere in the list. Looking up Pine wood will not return Pinewood.


To fix this, assuming that no other studios gave the same first 4 letters you could use:


=VLOOKUP("*"&LEFT(C4,4)&"*",SM_CC!B:C,2,FALSE)


Or replace the 4 with another number to call in the first 3 letters instead of 4.

 

[Chart68]

Thanks for this tip I'll give it a go as well.


Claire