3 criteria formula to return value (VLOOKUP, MATCH, INDEX?)

[lawi227]

Here are the three criteria my formula needs to meet:

First:
Cell $D$2 contains a drop-down (data validation) box which has four selection: "PWR/BWR", "Non-Fleet/Fleet", "Non-Regulated/Regulated" and "Multi-Unit/Single-Unit"
I need to return the value in the column based on the selection in the drop down box. See below:
$D$2="PWR/BWR" then search column L4:L458
$D$2="Non-Fleet/Fleet" then search column M4:M458
$D$2="Non-Regulated/Regulated" then search column N4:N458
$D$2="Multi-Unit/Single-Unit" then search column O4:O458

Second:
Once the correct column is identified that contains the value I'm looking for, I need to search column $A$4:$A$458 to equal $E$2.

Third:
I need to search column $B$4:$B$458 to equal $D4.

EXAMPLE.. Cell $E$2 contains a year (e.g., 2013), and I need to scan $A$4:$A$458 for where it equals 2013. It also must meet another criteria, so I need to scan $B$4:$B$458 to equal $D4. $D4 has a name (e.g., Clinton). Putting it all together now.. If $D$2 equals "PWR/BWR" I need to return the value in column L4:L458 where $E$2 equals "2013" and $D4 equals "Clinton"

I'd like this built into one formula. I do not care the order of the three criteria. I just outlined them in the above order to make it easier to explain. I thought a VLOOKUP, INDEX, MATCH, or a more complex combination would work. Let me know what other information you need.

Thanks so much for your help!! I always enjoy learning new formulas from Excel experts

 

[jorismoerings]

Hi,

Try and see if this what you're after:

Excel 2016 (Windows) 64 bit
	G
1	25
Sheet1

Cell Formulas
Range	Formula
G1	=INDEX(OFFSET(L1,0,MATCH($D$2,{"PWR/BWR";"Non-Fleet/Fleet";"Non-Regulated/Regulated";"Multi-Unit/Single-Unit"},0)-1,454,1),SUMPRODUCT(($A$4:$A$458=$E$2)*($B$4:$B$458=$D$4)*(ROW(4:458))))

 

[Tetra201]

@lawi227:

Try the following array formula. Note: it will return value for the last mach of '2013 Clinton' if there are several of them.

A question to you: why did you specify $D4 and not $D$4?

=INDEX($L$4:$O$458,MAX(IF(($A$4:$A$458=$E$2)*($B$4:$B$458=$D$4),ROW($A$4:$A$458)-ROW($A$4)+1)),LOOKUP($D$2,{"Multi-Unit/Single-Unit","Non-Fleet/Fleet","Non-Regulated/Regulated","PWR/BWR"},{4,2,3,1}))

 

[lawi227]

Thanks so much! This is fantastic. Mind sharing how this works? it looks like you assigned a value to each of the four criteria to shift the range left or right. I'm sure you can explain better than I.

I used $D4 because I am searching only names in column D but there is a long list so I need to click and drag down, as well as sideways.

@lawi227:

Try the following array formula. Note: it will return value for the last mach of '2013 Clinton' if there are several of them.

A question to you: why did you specify $D4 and not $D$4?

=INDEX($L$4:$O$458,MAX(IF(($A$4:$A$458=$E$2)*($B$4:$B$458=$D$4),ROW($A$4:$A$458)-ROW($A$4)+1)),LOOKUP($D$2,{"Multi-Unit/Single-Unit","Non-Fleet/Fleet","Non-Regulated/Regulated","PWR/BWR"},{4,2,3,1}))

 

[Tetra201]

You are welcome.

Here is how it works:

$L$4:$O$458 is a 455-row by 4-column array that contains your data.

MAX(IF(($A$4:$A$458=$E$2)*($B$4:$B$458=$D$4),ROW($A$4:$A$458)-ROW($A$4)+1)) returns the highest (max)
row number where 2013 and Clinton coincide. The row number is relative to row 4. Let's assume it returned '11'.

LOOKUP($D$2,{"Multi-Unit/Single-Unit","Non-Fleet/Fleet","Non-Regulated/Regulated","PWR/BWR"},{4,2,3,1}) compares
D2 to the first set in curly brackets and returns the corresponding number from the second set. Let's assume it's '2'.

Now, INDEX returns the value from row 11, column 2 of the 455-row by 4-column array.