get a value from multiple criteria combinations

[ladygr]

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

 

[Roger Govier]

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.

 

[David Biddulph]

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.

 

[ladygr]

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!

 

[ladygr]

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

 

[Roger Govier]

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