Lookup with two criteria

[futbol1097]

Ok - Here is what I have now -

test.xls
	A	B	C	D	E
1	Address	1ST	2ND	3RD	POOL
2	101	Yes	No	NO	No
3	102	Yes	No	NO	Yes
4	103	No	Yes	NO	No
5	104	No	No	Yes	No
6	105	Yes	No	NO	No
7	106	Yes	No	NO	Yes
8	107	No	Yes	NO	No
9	108	No	Yes	NO	Yes
10	109	Yes	No	NO	No
11	110	No	No	Yes	Yes
Sheet1

Now I want to create a new table that will do this:

test.xls
	H	I	J	K
1	Address	Level	Pool
2	101	1ST
3	102	1ST	Pool
4	103	2ND
5	104	3rd
6	105	1st
7	106	1st
8	107	2nd
9	108	2nd	Pool
10	109	2nd
11	110	3rd	Pool
Sheet1

I want the formula in column I to look up the Address in column A (ex: 104), then search across columns B:D to find where "Yes" is written. When "Yes" is found, I want it to return the word that is written in the respective Row 1 header. For example, 104 is on the "3rd" level b/c the "Yes" is in column D - so in cell I5 I want it to write whatever word is in D1 ("3rd"). This will allow me to consolidate rows B:D onto one column, because I am concerned about where the "Yes" is. I guess what I have here is, that I want the Lookup formula to not just go a certain # of columns across, be able to search a number of columns and determine in which column the criteria I want is (in this case - 3rd).

In column J, I want the formula to search two criteria: 1) search for the lookup value (104) in first table 2) find the column w/ header "Pool" in first table - when a "Yes" is there (both criteria met), I want it to return the word "Pool"; otherwise leave the cell blank. So it must find both criteria in order to return the value I want.

Is there such a thing as a Vlookup If - meaning look it up only If it meets a both criteria??

Thanks for the help guys,
E

 

[IML]

1 way,
in I2:
=INDEX($B$1:$D$1,MATCH("yes",B2:D2,0))
and copy down
in j2:
=IF(E2="yes",E$1,"")
and copy down.

 

[futbol1097]

Great! Thanks for the help.