Lookup with two criteria

futbol1097

New Member
Ok - Here is what I have now -
test.xls
ABCDE
2101YesNoNONo
3102YesNoNOYes
4103NoYesNONo
5104NoNoYesNo
6105YesNoNONo
7106YesNoNOYes
8107NoYesNONo
9108NoYesNOYes
10109YesNoNONo
11110NoNoYesYes
Sheet1

Now I want to create a new table that will do this:
test.xls
HIJK
21011ST
31021STPool
41032ND
51043rd
61051st
71061st
81072nd
91082ndPool
101092nd
111103rdPool
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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

IML

MrExcel MVP
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

New Member
Great! Thanks for the help.

Replies
7
Views
178
Replies
3
Views
128
Replies
1
Views
175
Replies
3
Views
719
Replies
5
Views
116