# Index/Match Lookup Formulas Returning Different Results

#### rob51852

##### Board Regular
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))

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### jasonb75

##### Well-known Member
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
Excel Formula:
``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
Excel Formula:
``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

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

Replies
1
Views
67
Replies
5
Views
63
Replies
2
Views
59
Replies
12
Views
123
Replies
9
Views
200

1,133,625
Messages
5,659,941
Members
418,538
Latest member
alc51103

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back