Multiple condition vlookup-type problem

[NicholasP]

I'm having a hard time conceptualizing this formula...Basically I'm trying to get a via 2 conditions like a vlookup..

The formula would look for the first instance where the value in column A was "A" and the value in column B is "Type1"...so the formula would return the value "10"...

Any suggestions would be greatly appreciated.

Thanks
Nick

Book1
	A	B	C	D
1	A	Type1	10
2	B	Type2	10
3	C	Type1	10
4	D	Type2	10
5	A	Type2	10
6	B	Type1	10
7	C	Type2	10
8	D	Type1	10
9	A	Type1	10
10	B	Type2	10
11	C	Type1	10
12	D	Type2	10
Sheet1

 

[Colin Legg]

Hi,

You can't do that with VLOOKUP().

Try this array formula instead:

=INDEX(C1:C12,MATCH(1,(A1:A12="A")*(B1:B12="Type1"),0))

When the formula is entered into the formula bar, you must complete the entry with CTRL+SHIFT+ENTER, not just ENTER, so that braces { } are inserted around the formula.

Hope that helps,

 

[NicholasP]

Ahh....nice application of Index/Match. I couldn't figure out how I could apply that and didn't think of an array formula...

Very nice -- Thanks
Nick