get a value from multiple criteria combinations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I'm at my wits end with this spreadsheet and could really use help. I'm a
newbie, and I have a worksheet 1 with 4 columns I need to factor. Each row
(from 2-501)one sheet 1 is a for a different student and each column factors
a specific test score. The columns I need to factor (which are I,J,R and U)
contain a Y or N (the value returned from a formula) if the specific test
score meets the criteria.

Now, I also have a sheet 2 in the same workbook, which contains multiple
lookup tables for the other formulas in sheet 1. What I am trying to do is
formulate a 2 column lookup table on sheet 2. Column 1 would be Y or N based
on matching a combination (see below) thus being TRUE or FALSE, and column 2
lists the recommendation for the combination that was matched.

Then, in a cell in the appropriate student's row on Sheet 1 put the
recommendation that matched the test result combination. Does that make
sense?? Here's my visual:

Column 1
Column 2

If I2="Y" and J2="N" and R2="Y" and U2="Y" "Book A, Unit 1"
If I2="Y" and J2="N" and R2="Y" and U2="N" "Book A, Unit 1"
If I2="Y" and J2="N" and R2="N" and U2="Y" "Book A, Unit 1"
If I2="Y" and J2="N" and R2="N" and U2="N" "Book A, Unit
1 w/emphasis on steps 3-6"
If I2="N" and J2="Y" and R2="Y" and U2="Y" "Book C, Unit
13"
If I2="N" and J2="Y" and R2="Y" and U2="N" "Book C, Unit
13"
If I2="N" and J2="Y" and R2="N" and U2="Y" "Book C, Unit
13"
If I2="N" and J2="Y" and R2="N" and U2="N" "Book C, Unit
13 w/emphasis on steps 3-6"
If I2="N" and J2="N" and R2="Y" and U2="Y or N" "Book C, Unit
13 w/emphasis on steps 1,2"
If I2="N" and J2="N" and R2="N" and U2="Y or N"
"Review/Acceleration Books C-F"

I need this to work seperately for each student (row), not just for row 2. I
had each IF statement on a seperate row on sheet 2, and a lookup on sheet 1,
but can't figure how to make it work for each row. Should I use an INDEX,
OFFSET, or MATCH entry?? I'm not familiar with those yet. Or, Maybe I'm
just dazed from looking too long.

ANY and ALL help is EXTREMELY appreciated!!!

Deborah
 
Hi Deborah

I would make the lookup table on sheet2 like this
YNYY "Book A, Unit 1"
YNYN "Book A, Unit 1"
YNNY "Book A, Unit 1"
YNNN "Book A, Unit 1 w/emphasis on steps 3-6"
etc.

Then on Sheet1
=VLOOKUP(I2&J2&R2&U2,Sheet2!$A$1:$B$10,2,0)

Change $A$1:$B$10 to the range reference where you create this lookup
table.
 
If you're sure that each of the 4 inputs has either a Y or N, then try:

=IF(I2="Y",IF(J2="N",IF(R2="Y","Book A, Unit 1",IF(U2="Y","Book A, Unit
1","Book A, Unit 1 w/emphasis on steps
3-6")),"undefined"),IF(J2="Y",IF(R2="Y","Book C, Unit 13",IF(U2="Y","Book C,
Unit 13","Book C, Unit 13 w/emphasis on steps 3-6")),IF(R2="Y","Book C, Unit
13 w/emphasis on steps 1,2","Review/Acceleration Books C-F")))
Copy down the remaining rows.
 
Roger,

This looks like it is a great option, but how do I use a wildcard for the
last two IF's? The fourth test for entries 9 and 10 can be either Y -OR- N.
How can I show that? Should I put one of each statements inthe lookup (to
make a total of 14 possibles?)Otherwise the formula works great!!

Deborah

I appreciate David's response too!
 
Roger,

I did use a total of 14 rows on sheet 2 for the list lookup and it works
great. Thank you, thank you, Thank you.

My final formula on Sheet 1 is simply:

=IF(ISNA(VLOOKUP(I2&J2&R2&U2,Conversions!$J$2:$K$14,2,0)),"",VLOOKUP(I2&J2&R2&U2,Conversions!$J$2:$K$14,2,0))

Now if I can wrap my brain around the reasoning, maybe I will remeber it and
be able to adapt it to another similar situation later.

Deborah
 
Hi Deborah

You're very welcome. Thanks for the feedback with your eventual
solution.
Glad you got it figured out.

I use the technique of concatenating many fields together quite a lot.
It really does cut down on the jumping through hoops with lots of IF
statements and multiple terms in Sumproduct.

When the data is not as simple as your Y or N results, then I like to
use a "!" pipe symbol between each concatenation
e.g. A1&"|"&B1
so I can distinguish between 2|23 and 22|3
 
Back
Top