Excel VLOOKUP with 2 Search Values

Question

I have a formula that acts as VLOOKUP with two values. It looks for x in column A and y in column B, when both found on the same row it returns the value in column C. The problem with this formula is that it doesn't stop calculating, each time I change a value in any field (not just A, B or C) or just scroll down the page it starts calculating making it impossible to use.

The formula is ended with Ctrl + Shift + Return

{=INDEX(C:C;MATCH("x"&"y";A1:A99&B1:B99;0))}

Anybody know how to get the same result without slowing excel down.

Thanks, Gareth

Solution 1

You can try an alternative with LOOKUP

=LOOKUP(2;1/(A1:A99="x")/(B1:B99="y");C1:C99)

Solution 2

If you can afford to make the spreadsheet ugly by adding a helper column, then maybe in a new column (to the left of the column to be returned) write down the ="x"&"y" values and go with a standard Vlookup from there.

Array formulas WILL eventually bog you down.