CODEWITHSUNDEEP
excelvlookup
Gareth Maclean
Gareth Maclean

Reputation: 109

Excel VLOOKUP with 2 Search Values

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

Upvotes: 2

Views: 10684

Answers (2)

MarioTheHedgehog
MarioTheHedgehog

Reputation: 306

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.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46341

You can try an alternative with LOOKUP

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

Upvotes: 1

Related Questions