CODEWITHSUNDEEP
excelindexingexcel-formulamatching
saidtheJesse
saidtheJesse

Reputation: 27

INDEX MATCH not returning data correctly

I have an Index/match and it isn't returning all names correctly. The error seems to be if there is a duplicate last name but I don't know how to correct it. Here is my formula:

=IFERROR(INDEX('[Data File]Sheet1'!$E:$E, MATCH(A2, '[Data File]Sheet1'!$B:$B & "," & '[Data File]Sheet1'!$C:$C, 0)), "")

The idea is to return a blank unless what is input in column A matches the data from Data File Sheet1. Data File Sheet1 Column B is Last names and Column C is first names. I have no control over this file.

My file logs names as first, last in column A. When sucessful this should return the matching line E from Data File Sheet1.

Like I said, this seems to work if the last name is unique but breaks if there is a duplicate.

The first image is what the data is input like in my program. The second is what the data looks like in the sheet I pull data from.

Column A is the First name, Last name

Data File Sheet1 lists names as column B last name column C first name

In these images only Fake Name would work with my code, Should Work and Might Work will both return my error line, i.e. a blank.

Upvotes: 0

Views: 175

Answers (1)

Unnamed Mistress
Unnamed Mistress

Reputation: 83

Create a Key column for your name match to avoid this. You can use a concat of the first and last name to get better matches.

Enter this into your new column =(Firstnamecell&" "Lastnamecell) Use this key column in your formula Match(A2, referenceyourKeyColumn, 0))

Upvotes: 1

Related Questions