Lookup with two criteria

futbol1097

New Member
Joined
Dec 14, 2005
Messages
32
Ok - Here is what I have now -
test.xls
ABCDE
1Address1ST2ND3RDPOOL
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
1AddressLevelPool
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
 

Some videos you may like

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
Joined
Feb 15, 2002
Messages
1,743
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,017
Messages
5,569,640
Members
412,284
Latest member
Daibear
Top