Solved: vlookup with multiple criteria

[chungtinhlak]

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]

Try this array formula

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

[chungtinhlak]

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]

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]

i get it, thanks