CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010
Deepak
Deepak

Reputation: 473

How to match multiple columns in different sheets in Excel

enter image description here I would like to match the values of Column B and D in Sheet 2 , compare Column D & Column E and return the value of Column E in Sheet1-Column B. I used the formulas below and it is not working. The problem is most of Column A values in Sheet 1 and Sheet 2 are different. So I need to match both Column A. I will enter this formula in Sheet 1 Column B.

How can i change the formula? Help me

=INDEX(Sheet2!$E:$E; AND(MATCH(Sheet1!$A2; Sheet2!$A:$A; 0);MATCH(Sheet2!$B2; Sheet2!$D:$D; 0)))

=INDEX(Sheet2!$E:$E; MATCH(Sheet2!$B2; Sheet2!$D:$D; 0))

Upvotes: 3

Views: 9952

Answers (1)

QHarr
QHarr

Reputation: 84475

In B2 of sheet1 you could put the following:

=VLOOKUP(VLOOKUP(A2,Sheet2!A:B,2,FALSE),Sheet2!D:E,2,FALSE)

The inner lookup =VLOOKUP(A2,Sheet2!A:B,2,FALSE) returns the code e.g. A and then the outer lookup =VLOOKUP(innerlookupvalue,Sheet2!D:E,2,FALSE), uses this value to do a lookup against the range containing the content values.

You can drag this down for as many rows as you have values. You will need to decide how to handle values that are not found.

What may be a problem is when you say "The problem is most of Column A values in Sheet 1 and Sheet 2 are different", you will get #N/A returned as you can't lookup a non-match. As i said above, you would need to determine how to handle this.

Upvotes: 3

Related Questions