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