0

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.

Improve this question
1
  • Difficult to provide advice without seeing sample data. In MATCHyou are concatenating B and C columns, but we don´t know what is in A2. Commented Nov 13, 2022 at 19:10

1 Answer 1

Reset to default
1

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))

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, this is a simple and effective solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.