VLOOKUP AND FIND THE SECOND MATCH

[Deano]

Hi

Can I modify a VLOOKUP formula to not find the first match, but the second, third, etc.

 

[Aladin Akyurek]

Sorry to resurrect this thread but I am trying to use the UDF suggested and am having trouble

The problem with this is that my lookup isnt an exact match to the list it looks to

I have managed to doctor a vlookup function so that it looks for the item anywhere in the string using the following formula:

=VLOOKUP("*" & A1 & "*",'Sheet1'!D2:J241,7,FALSE)

However if i try and use the same sort of thing within the UDF or even the Index formula it returns no result. I am assuming that is because it doesnt recognise what I am looking for ie "*" & A1 & "*", but it is strange that the vlookup formula will recognise it

Any help appreciated

The equivalent formula with Index/Match would be:

=INDEX(Sheet1!$J$2:$J$241,MATCH("*"&A1&"*",Sheet1!$D$2:$D$241,0))