Reputation:
I'm trying to compare multiple column's value between 2 different sheets and return the value of an other column for the match data.
For example:
sheet1
and sheet2
I need to get in sheet1 G:G (D)
the value of sheet2 D:D (DD)
Where A
and B
and C
are match. You can see the values I should get in sheet1,G:G
in column H:H
(check)
file in google drive: link
I was trying to work with VLOOKUP
and AND
but couldn't make it...
Will really appreciate your help guys!
Thanks ahead!
Upvotes: 0
Views: 143
Reputation: 35990
Assuming the top sheet is Sheet1 and the values are in column H, and you want the formula in the second sheet, you could use in Sheet2!D2:
=INDEX(Sheet1!$H$1:$H$10,MATCH(Sheet2!A2&Sheet2!B2&Sheet2!C2,INDEX(Sheet1!$A$1:$A$10&Sheet1!$B$1:$B$10&Sheet1!$C$1:$C$10,0),0))
If you want the formula in column H on the first sheet, you need
=INDEX(Sheet2!$D$1:$D$10,MATCH(Sheet1!A2&Sheet1!B2&Sheet1!C2,INDEX(Sheet2!$A$1:$A$10&Sheet2!$B$1:$B$10&Sheet2!$C$1:$C$10,0),0))
As an alternative, you could create a helper column and concatenate the values. Place that as the first column and you can use a vlookup.
Upvotes: 1
Reputation: 23
Your screenshot does not really include the column names but if you need to check your columns A,B and C, you can do =if(AND(A,B,C), vlookup..). If you can load the actual sheets and mask the values, we can take a look at that as well.
shred
Upvotes: 0