Index/Match Lookup Formulas Returning Different Results

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
183
Office Version
  1. 2016
Platform
  1. Windows
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

Add Bullets to Range
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
Joined
Dec 30, 2008
Messages
12,960
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 27, 2016
Messages
183
Office Version
  1. 2016
Platform
  1. Windows
Thanks Jason. I should have explained that the lookup criteria in F3 and C12 are unique IDs.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top