Use VLookup to search for multiple variation of a name

[mss90]

I am currently attempting to get VLookup to look for multiple variations of a name. For example, I have a list with names and values. By using VLookup I wish to find the value for either X-16/16-X/X 16/ 16 X, only 1 of these will be in the table at one point so Im thinking it would be great if I somehow could get VLookup to search for all of them in 1 single formula.

See attached example

Please advice

[shukla.ankur281190]

Hey can you put some more examples ??? It would easy to workaround

[XOR LX]

Hi,

=LOOKUP(1,0/COUNTIF(E2:E5,A9:A11),B9:B11)

Regards

[mss90]

Thanks for quick reply, why "1,0/"? The formula does not seem to work here, Im getting #DIV/0!

[mss90]

Thanks for quick reply, I have uploaded some more examples. So the clue here is to look for multiple similar name corresponding to whatever is in the list by using the alternative name list

[shukla.ankur281190]

May be try

B2=IFERROR(VLOOKUP(INDEX($E$2:$E$11,MATCH(A2,$E$2:$E$11,0)+1),$A$9:$B$12,2,0),VLOOKUP(A2,$A$9:$B$12,2,0))

[XOR LX]

But how do we know which of the entries in the Alternative Lookup names list is associated with a given search entry?

You might be able to tell by sight that, for the entry in cell A2 ("X-16"), for example, we should use the four options "16X", "X16", "X-16" and "16-X" from the Alternative Lookup names list, but what logic can we use to tell Excel to use these four only? This is bordering on 'fuzzy' matching, which is extremely problematic. As such, this is most likely only going to be achievable if you can give more strict rules regarding which 'variations' of a given search term should be considered, e.g. for a given entry search in the Alternative Lookup names list for:

1) That entry
2) That entry with the hyphen removed
3) That entry with the characters in reverse order
...
etc.

Regards

[mss90]

But how do we know which of the entries in the Alternative Lookup names list is associated with a given search entry?

You might be able to tell by sight that, for the entry in cell A2 ("X-16"), for example, we should use the four options "16X", "X16", "X-16" and "16-X" from the Alternative Lookup names list, but what logic can we use to tell Excel to use these four only? This is bordering on 'fuzzy' matching, which is extremely problematic. As such, this is most likely only going to be achievable if you can give more strict rules regarding which 'variations' of a given search term should be considered, e.g. for a given entry search in the Alternative Lookup names list for:

1) That entry
2) That entry with the hyphen removed
3) That entry with the characters in reverse order
...
etc.

Regards

What If I just have 1 list per variation, so all the X16 variations is in its own list?

[mss90]

May be try

B2=IFERROR(VLOOKUP(INDEX($E$2:$E$11,MATCH(A2,$E$2:$E$11,0)+1),$A$9:$B$12,2,0),VLOOKUP(A2,$A$9:$B$12,2,0))

This does not seem to work for me, does it work for you?

[mss90]

New example with separated alternative name lists (see attached)

[XOR LX]

What If I just have 1 list per variation, so all the X16 variations is in its own list?

Yes, that would work. Or, better still, you could have a single list with all variations to consider for a given entry, e.g.:

Name	Alternative Lookup Names
X-16	16X
X-16	X16
X-16	X-16
X-16	16-X
C1	C1
C1	1C
A1	A1
A1	1A
B1	B1
B1	1B

Can you attach a workbook with some new examples to that effect?

Regards

[mss90]

Yes, that would work. Or, better still, you could have a single list with all variations to consider for a given entry, e.g.:

Name	Alternative Lookup Names
X-16	16X
X-16	X16
X-16	X-16
X-16	16-X
C1	C1
C1	1C
A1	A1
A1	1A
B1	B1
B1	1B

Can you attach a workbook with some new examples to that effect?

Regards

Yeah I did think about that already but as this is a part of a huge excel workbook that solution will cause a lot of issues "downstream". Is it not possible to just have a list with alternative names?

[mss90]

What about using double VLookup (See attached excel spreadsheet).

So first run a Vlookup to find the ID/Name thats has been used. Then paste that findings by ignoring the N/A cells into another VLookup to find the corresponding value. Would that work? Im not sure how to get copy the finding og the first Vlookup tho

[XOR LX]

Yeah I did think about that already but as this is a part of a huge excel workbook that solution will cause a lot of issues "downstream". Is it not possible to just have a list with alternative names?

But we still need separate lists for each of the search terms, no, as per your latest example? The way I've laid it out is no different in terms or quantity of entries, yet in terms of facilitating the required formula set-up it's an enormous improvement.

Regards

[mss90]

So is it not possible then to just have a synonym list and do Vlookup on multiple different names?

[XOR LX]

Of course. That's what my list was! As was yours - my only wish would be to have the list in the format I gave rather than yours.

Regards

[mss90]

Alright cheers. I cant have that list due to the setup of my spreadsheet, its not going to work I'm afride.