PDA

View Full Version : Solved: vlookup with multiple criteria



chungtinhlak
09-24-2009, 09:02 AM
I used vlookup a lot but I've always been using it with single criteria. but now, I need it for multiple criteria. For example, column A2 - A5 = A and B2- B5 is P2, P3, P4. I want to be able to lookup for A and A5.

Please refer to the file and picture below.

i want to be able to lookup C,P2 and return 55 for billy.

thanks

Bob Phillips
09-24-2009, 09:12 AM
Try this array formula

=INDEX(A1:G21,MATCH(1,(A1:A21="C2")*(B1:B21="P"),0),MATCH("Billy",A1:G1,0))

chungtinhlak
09-24-2009, 09:21 AM
wow, thanks a lot xld, i have to change some names around but works perfect. I looked up index in google but don't quite understand how that works, can you educate me a little on that?

does this mean i can do this with 3 criteria also? is there a limit?

Bob Phillips
09-24-2009, 09:33 AM
INDEX will return the intersection of the stipulated row and column within a given range. So

INDEX(A1:M10,5,4) returns the value in cell D5, whereas

INDEX(B3:M10,5,4) returns the value in cell E7.

The two matches are used to calculate those row and column numbers.

chungtinhlak
09-24-2009, 10:47 AM
i get it, thanks