Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default get a value from multiple criteria combinations

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default get a value from multiple criteria combinations

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 620
Default get a value from multiple criteria combinations

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.
--
David Biddulph

"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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default get a value from multiple criteria combinations

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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default get a value from multiple criteria combinations

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






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default get a value from multiple criteria combinations

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
return multiple rows of data based on criteria steve_sr2 Excel Worksheet Functions 8 May 20th 23 07:47 PM
Count Unique Values with Multiple Criteria JohnV Excel Worksheet Functions 3 April 17th 06 06:00 PM
Multiple Criteria for Conditional Formatting Dave Y Excel Worksheet Functions 4 March 21st 06 07:38 PM
COUNTIF or SUM function (Multiple criteria) HELP!! Australia Excel Worksheet Functions 3 September 19th 05 07:39 AM
Multiple Criteria (add or subtract) MJSlattery Excel Discussion (Misc queries) 1 June 11th 05 05:38 PM


All times are GMT +1. The time now is 07:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"