Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple rows of data based on criteria | Excel Worksheet Functions | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
Multiple Criteria for Conditional Formatting | Excel Worksheet Functions | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
Multiple Criteria (add or subtract) | Excel Discussion (Misc queries) |