Index Match across 2 sheets not working

0
votes

I am trying the following function but it is not working:

=INDEX(Accounts!$A$2:$AB$2820,MATCH($A$2,Accounts!$E$2:$E$2820,0),6)

I get a #N/A result

Both sheets are in the same workbook.

Accounts has the Account Name (Column E) and Account ID (Column F)

Current Sheet (Customers) has the Account Name (Column A) but not Account ID.

I am trying to match the Account Names in the current sheet to the Account Names in the Account Sheet and then pull in the Account ID.

Hope that makes sense. Have spent hours trying to make this work. Please help!

2
excelindexingmatch
@pnuts, it's the column number in index(range, row, column) - teylyn
Your formula is correct. There's a reason in your data that makes it fail to find a match. More information is needed about the data. Check for extra spaces in the names, for example. - A.S.H
Try sorting the lookup column(E on Accounts) and using 1 instead of 0 in the third criterion of the match. If you get a return start looking at the data to find out why it does not find a match. Remember 0 indicates an exact match. - Scott Craner
@ScottCraner Caution with that advice about 1 as the third parameter of Match. If people don't understand the concept of the approximate match, they may fall victim to unintentional approximate matches, which may NOT be what they want, and may lead to totally wrong results in their spreadsheets. Approximate match may be the solution, but #N/A may be valid, too. It REALLY depends on the context. - teylyn
@teylyn I was trying to show that the problem was not the formula but the data, hence the ` If you get a return start looking at the data to find out why it does not find a match.` - Scott Craner

2 Answers

0
votes

Thank you for your responses. I looked into the formula further and discovered two reasons why it wasn't working:

  1. The first item did not have a match causing #N/A
  2. The formula kept looking at that first result for all subsequent rows.

Original Formula: =INDEX(Accounts!$A$2:$AB$2820,MATCH($A$2,Accounts!$E$2:$E$2820,0),6)

Correct Formula: =INDEX(Accounts!$A$2:$AB$2820,MATCH(A2,Accounts!$E$2:$E$2820,0),6)

When you pull down the original formula onto subsequent rows it stays exactly the same. When you pull down the correct formula the values for the lookup updates correctly.

-3
votes

index needs to be 1 column, and get rid of the 6

=INDEX(Accounts!$A$2:$A$2820,MATCH($A$2,Accounts!$E$2:$E$2820,0))

the index is the column you want your result from, in this case column A on accounts sheet, the match is the column that has the criteria you are loooking for in this case column E on Accounts sheet

if this still return an NA then you are not making a match with your value, check to make sure the column that you are matching has the same value, even a space can make it return an NA