Index/Match Lookup Formulas Returning Different Results

[rob51852]

Hi,

I am using the following formulas to lookup names in a table in different workbooks. The tables areidentical and sorted in the same way.

The issue is that the tables sometime contain different versions of the name (eg Rob Smith and R Smith). I don't care which variation is found, just that the formulas return the same variation.

I had thought that Index/Match returned the first matching result but apparently not.

Can anyone suggest how to make it work?

Thanks

=IFNA(INDEX(LineupsTable[playerName],MATCH(CI2,LineupsTable[playerId],0)),"")

=INDEX(LineupsTable[playerName],MATCH($F$3,LineupsTable[playerId],0))

 

[jasonb75]

To such formulas, different versions of the same name are different names. Exact match formulas will compare R Smith to R smith and Rob Smith to Rob Smith, the 2 are not interchangeable.

Not recommended, but you could work around it by changing the match part to something like

MATCH(REPLACE(CI2,2,FIND(" ",CI2)-1,"* "),LineupsTable[playerId],0)

As with any workaround, this is likely to fail sooner or later, the most obvious reason being if there are 2 or more possible correct matches in the table.
You could try double matching by using something like

IFERROR(MATCH(CI2,LineupsTable[playerId],0),MATCH(REPLACE(CI2,2,FIND(" ",CI2)-1,"* "),LineupsTable[playerId],0))

which could possibly reduce the number of incorrect matches, but not eliminate them altogether.

 

[rob51852]

Thanks Jason. I should have explained that the lookup criteria in F3 and C12 are unique IDs.