user1301428 February 2016

How to use a VLOOKUP to get a column value based on another column value?

I have two Excel sheets, the first one looks like this:

enter image description here

And this is the second one:

enter image description here

Basically, I would like to fill in the column Value in the second sheet with the text from the Answer Column in the first sheet, but only when the value of the Question column is 3.

If there was no condition involved, I would simply use a VLOOKUP and be done, but is it possible to do a conditional VLOOKUP like this?

Answers


Sixthsense February 2016

In J2 Cell of Sheet2

=IFERROR(INDEX(Sheet1!D2:D10,MATCH(TRUE,--(Sheet1!B2:B10=Sheet2!H2)*Sheet1!C2:C10=SUMPRODUCT(MAX((Sheet1!B2:B10=Sheet2!H2)*Sheet1!C2:C10)),0)),"")

Array formula requires CTRL+SHIFT+ENTER instead of normal enter.

Post Status

Asked in February 2016
Viewed 2,607 times
Voted 13
Answered 1 times

Search




Leave an answer