CODEWITHSUNDEEP
excelindexingmatch
Christópher Abreu Rosario
Christópher Abreu Rosario

Reputation: 1

Index Match across 2 sheets not working

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!

Upvotes: 0

Views: 6045

Answers (2)

Christópher Abreu Rosario
Christópher Abreu Rosario

Reputation: 1

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.

Upvotes: 0

user3005775
user3005775

Reputation: 306

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

Upvotes: -3

Related Questions