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


--
Regards

Roger Govier


"ladygr" wrote in message
...
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$1 4,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

"ladygr" wrote:

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 Govier" wrote:

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.

--
Regards

Roger Govier


"ladygr" wrote in message
...
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