SOLVED

# Vlookup with 2 scenarios

Brass Contributor

# Vlookup with 2 scenarios

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

# Re: Vlookup with 2 scenarios

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.

# Re: Vlookup with 2 scenarios

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

# Re: Vlookup with 2 scenarios

Does this do what you want?

# Re: Vlookup with 2 scenarios

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.

# Re: Vlookup with 2 scenarios

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.

# Re: Vlookup with 2 scenarios

@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

# Re: Vlookup with 2 scenarios

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

# Re: Vlookup with 2 scenarios

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

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

# Re: Vlookup with 2 scenarios

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