SOLVED

Vlookup with 2 scenarios

Brass Contributor

Hi All, I am trying to formulate a formula to lookup two values. One is the tag number and the other the date and then it should return the test results in column 4 of the test results tab.

 

=IFERROR(VLOOKUP(A3, Table10, 1, FALSE)+VLOOKUP(X3, Table10, 2, FALSE)=VLOOKUP(A3,Table10,4,FALSE), "Not Tested")

8 Replies

@Janedb 

Do you mean that you want to find the row in Table10 in which the first column matches A3 and the second column matches X3, and return the value from the fourth column in that row? If so:

 

=IFERROR(INDEX(Table10[Fourth Column], MATCH(1, (Table10[First Column]=A3)*(Table10[Second Column]=X3), 0)), "Not Tested")

 

Replace Fourth Column with the actual name of the 4th column of the table, etc.

 

If you want something else, please explain.

@HansVogelaar Hi, Please see sample

 

I want to match A3 & X3 and it should return the result in table 10 column 4.

@Janedb 

Does this do what you want?

Hi Hans, it is exactly what I need but the formula does not work on my version of excel. I have Excel 2013. I see you have{ } in front and at end of formula but as soon as I press enter this disappears and then the formula does not work.

@Janedb 

You have to confirm the formula by pressing Ctrl+Shift+Enter. That turns the formula into a so-called array formula. You'll see { } around the formula in the formula bar to indicate that it is an array formula.

@HansVogelaar, thank you very much. It didn't work until I did the Ctrl+Shift+Enter in the correct order.
best response confirmed by Janedb (Brass Contributor)
Solution

@Janedb 

If to modify slightly

=IFERROR(INDEX(Table10[Result], MATCH(1, INDEX( (Table10[Tag]=A2)*(Table10[Date]=X2),0,1), 0)), "Not Tested")

it shall work without Ctrl+Shift+Enter

Thank you!, This one is working without the Ctrl+Shift+Enter.
1 best response

Accepted Solutions
best response confirmed by Janedb (Brass Contributor)
Solution

@Janedb 

If to modify slightly

=IFERROR(INDEX(Table10[Result], MATCH(1, INDEX( (Table10[Tag]=A2)*(Table10[Date]=X2),0,1), 0)), "Not Tested")

it shall work without Ctrl+Shift+Enter

View solution in original post