Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
One way...
Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Thanks. I tried your suggestion, but am still getting a #N/A error. I have
entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Here's a small sample file that demonstrates this. I included a second
formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Great! Many thanks. This works. I guess I just had an error when I entered
the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Another question....are there any limitations that you are aware of when
using these formulas? For example, I've put the data table (about 65000 rows) on one tab and the lookup formula in another tab. I also have the data ranges setup to be the entire column on the data tab of the spreadsheet. I can't get the formula to work in this situation. -- dat842 "dat842" wrote: Great! Many thanks. This works. I guess I just had an error when I entered the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Thanks for your help. I've tried both formulas across tabs. Here they are. Do
you see any issues? {=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))} =SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H) -- dat842 "dat842" wrote: Another question....are there any limitations that you are aware of when using these formulas? For example, I've put the data table (about 65000 rows) on one tab and the lookup formula in another tab. I also have the data ranges setup to be the entire column on the data tab of the spreadsheet. I can't get the formula to work in this situation. -- dat842 "dat842" wrote: Great! Many thanks. This works. I guess I just had an error when I entered the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
What version of Excel are you using?
Unless you're using Excel 2007 you *can't* use entire columns as range references with either an array entered formula or a SUMPRODUCT formula. Trapping the error will make the formula twice as long (unless you're using Excel 2007). Any version of Excel (still array entered): =IF(ISNA(MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F 2:F10,IF(A9<=G2:G10,1)))),0)),"Unavailable",INDEX( H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F2: F10,IF(A9<=G2:G10,1)))),0))) Another consideration: If your data set is ~65,000 rows this error trapped formulas will be somewhat slow to calculate since it has to calculate twice if/when there isn't an error. It may be better to use 2 cells, one with the basic formula and then a smaller formula that tests the cell with the basic formula. Something like this: A1 = basic formula (array formula without the error trap). This will return either the correct number or the #N/A error. A2: formula =IF(ISNA(A1),"Unavailable",A1) Excel 2007 only (still array entered): =IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable") -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks for your help. I've tried both formulas across tabs. Here they are. Do you see any issues? {=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))} =SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H) -- dat842 "dat842" wrote: Another question....are there any limitations that you are aware of when using these formulas? For example, I've put the data table (about 65000 rows) on one tab and the lookup formula in another tab. I also have the data ranges setup to be the entire column on the data tab of the spreadsheet. I can't get the formula to work in this situation. -- dat842 "dat842" wrote: Great! Many thanks. This works. I guess I just had an error when I entered the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Thanks. I am using Excel 2007. Good tip about the speed issue and the error.
I'm still having trouble getting the formula to work for the entire column. Here's what I've done. I have the formula setup on one sheet and the small sample table on the second sheet. The formula contains the ranges for the sample table. Everything works fine. Then I grab the entire data set and paste it into sheet two over the top of the sample data. I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. Any ideas? -- dat842 "T. Valko" wrote: What version of Excel are you using? Unless you're using Excel 2007 you *can't* use entire columns as range references with either an array entered formula or a SUMPRODUCT formula. Trapping the error will make the formula twice as long (unless you're using Excel 2007). Any version of Excel (still array entered): =IF(ISNA(MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F 2:F10,IF(A9<=G2:G10,1)))),0)),"Unavailable",INDEX( H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F2: F10,IF(A9<=G2:G10,1)))),0))) Another consideration: If your data set is ~65,000 rows this error trapped formulas will be somewhat slow to calculate since it has to calculate twice if/when there isn't an error. It may be better to use 2 cells, one with the basic formula and then a smaller formula that tests the cell with the basic formula. Something like this: A1 = basic formula (array formula without the error trap). This will return either the correct number or the #N/A error. A2: formula =IF(ISNA(A1),"Unavailable",A1) Excel 2007 only (still array entered): =IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable") -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks for your help. I've tried both formulas across tabs. Here they are. Do you see any issues? {=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))} =SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H) -- dat842 "dat842" wrote: Another question....are there any limitations that you are aware of when using these formulas? For example, I've put the data table (about 65000 rows) on one tab and the lookup formula in another tab. I also have the data ranges setup to be the entire column on the data tab of the spreadsheet. I can't get the formula to work in this situation. -- dat842 "dat842" wrote: Great! Many thanks. This works. I guess I just had an error when I entered the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . . |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Then I grab the entire data set and paste it
into sheet two over the top of the sample data I'm not following you on that. Why would you do that? What purpose does it serve? I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. What does "doesn't work" mean exactly? You get an error? An incorrect result? -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I am using Excel 2007. Good tip about the speed issue and the error. I'm still having trouble getting the formula to work for the entire column. Here's what I've done. I have the formula setup on one sheet and the small sample table on the second sheet. The formula contains the ranges for the sample table. Everything works fine. Then I grab the entire data set and paste it into sheet two over the top of the sample data. I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. Any ideas? -- dat842 "T. Valko" wrote: What version of Excel are you using? Unless you're using Excel 2007 you *can't* use entire columns as range references with either an array entered formula or a SUMPRODUCT formula. Trapping the error will make the formula twice as long (unless you're using Excel 2007). Any version of Excel (still array entered): =IF(ISNA(MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F 2:F10,IF(A9<=G2:G10,1)))),0)),"Unavailable",INDEX( H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F2: F10,IF(A9<=G2:G10,1)))),0))) Another consideration: If your data set is ~65,000 rows this error trapped formulas will be somewhat slow to calculate since it has to calculate twice if/when there isn't an error. It may be better to use 2 cells, one with the basic formula and then a smaller formula that tests the cell with the basic formula. Something like this: A1 = basic formula (array formula without the error trap). This will return either the correct number or the #N/A error. A2: formula =IF(ISNA(A1),"Unavailable",A1) Excel 2007 only (still array entered): =IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable") -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks for your help. I've tried both formulas across tabs. Here they are. Do you see any issues? {=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))} =SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H) -- dat842 "dat842" wrote: Another question....are there any limitations that you are aware of when using these formulas? For example, I've put the data table (about 65000 rows) on one tab and the lookup formula in another tab. I also have the data ranges setup to be the entire column on the data tab of the spreadsheet. I can't get the formula to work in this situation. -- dat842 "dat842" wrote: Great! Many thanks. This works. I guess I just had an error when I entered the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . . |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Then I grab the entire data set and paste it
into sheet two over the top of the sample data I'm not following you on that. Why would you do that? What purpose does it serve? I'm adding in the entire data table which I retrieved out of a SQL db onto sheet two. This allows me to test the formula against a small table of data, and then add the full table later. I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. What does "doesn't work" mean exactly? You get an error? An incorrect result? Doesn't work means that I get an "#N/A" error. When I know the value I'm looking up exists in the table. -- dat842 "T. Valko" wrote: Then I grab the entire data set and paste it into sheet two over the top of the sample data I'm not following you on that. Why would you do that? What purpose does it serve? I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. What does "doesn't work" mean exactly? You get an error? An incorrect result? -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I am using Excel 2007. Good tip about the speed issue and the error. I'm still having trouble getting the formula to work for the entire column. Here's what I've done. I have the formula setup on one sheet and the small sample table on the second sheet. The formula contains the ranges for the sample table. Everything works fine. Then I grab the entire data set and paste it into sheet two over the top of the sample data. I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. Any ideas? -- dat842 "T. Valko" wrote: What version of Excel are you using? Unless you're using Excel 2007 you *can't* use entire columns as range references with either an array entered formula or a SUMPRODUCT formula. Trapping the error will make the formula twice as long (unless you're using Excel 2007). Any version of Excel (still array entered): =IF(ISNA(MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F 2:F10,IF(A9<=G2:G10,1)))),0)),"Unavailable",INDEX( H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F2: F10,IF(A9<=G2:G10,1)))),0))) Another consideration: If your data set is ~65,000 rows this error trapped formulas will be somewhat slow to calculate since it has to calculate twice if/when there isn't an error. It may be better to use 2 cells, one with the basic formula and then a smaller formula that tests the cell with the basic formula. Something like this: A1 = basic formula (array formula without the error trap). This will return either the correct number or the #N/A error. A2: formula =IF(ISNA(A1),"Unavailable",A1) Excel 2007 only (still array entered): =IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable") -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks for your help. I've tried both formulas across tabs. Here they are. Do you see any issues? {=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))} =SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H) -- dat842 "dat842" wrote: Another question....are there any limitations that you are aware of when using these formulas? For example, I've put the data table (about 65000 rows) on one tab and the lookup formula in another tab. I also have the data ranges setup to be the entire column on the data tab of the spreadsheet. I can't get the formula to work in this situation. -- dat842 "dat842" wrote: Great! Many thanks. This works. I guess I just had an error when I entered the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . . . |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match function with nested if
Well, I don't know what to tell you. The formula works as I demonstrated in
the sample file. Something you're doing with the data manipulation seems to be causing the problem and I can't duplicate what you're doing or the data you're working with on my end. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Then I grab the entire data set and paste it into sheet two over the top of the sample data I'm not following you on that. Why would you do that? What purpose does it serve? I'm adding in the entire data table which I retrieved out of a SQL db onto sheet two. This allows me to test the formula against a small table of data, and then add the full table later. I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. What does "doesn't work" mean exactly? You get an error? An incorrect result? Doesn't work means that I get an "#N/A" error. When I know the value I'm looking up exists in the table. -- dat842 "T. Valko" wrote: Then I grab the entire data set and paste it into sheet two over the top of the sample data I'm not following you on that. Why would you do that? What purpose does it serve? I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. What does "doesn't work" mean exactly? You get an error? An incorrect result? -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I am using Excel 2007. Good tip about the speed issue and the error. I'm still having trouble getting the formula to work for the entire column. Here's what I've done. I have the formula setup on one sheet and the small sample table on the second sheet. The formula contains the ranges for the sample table. Everything works fine. Then I grab the entire data set and paste it into sheet two over the top of the sample data. I adjust the ranges in the formula to reflect the new dataset and the formula doesn't work. Any ideas? -- dat842 "T. Valko" wrote: What version of Excel are you using? Unless you're using Excel 2007 you *can't* use entire columns as range references with either an array entered formula or a SUMPRODUCT formula. Trapping the error will make the formula twice as long (unless you're using Excel 2007). Any version of Excel (still array entered): =IF(ISNA(MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F 2:F10,IF(A9<=G2:G10,1)))),0)),"Unavailable",INDEX( H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF(A9=F2: F10,IF(A9<=G2:G10,1)))),0))) Another consideration: If your data set is ~65,000 rows this error trapped formulas will be somewhat slow to calculate since it has to calculate twice if/when there isn't an error. It may be better to use 2 cells, one with the basic formula and then a smaller formula that tests the cell with the basic formula. Something like this: A1 = basic formula (array formula without the error trap). This will return either the correct number or the #N/A error. A2: formula =IF(ISNA(A1),"Unavailable",A1) Excel 2007 only (still array entered): =IFERROR(INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E 10=3,IF(A9=F2:F10,IF(A9<=G2:G10,1)))),0)),"unavai lable") -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks for your help. I've tried both formulas across tabs. Here they are. Do you see any issues? {=INDEX(Prices!H:H,MATCH(1,IF(Prices!D:D=Sheet1!F$ 6,IF(Prices!C:C=I6,IF(Sheet1!F$8=Prices!D:D,IF(Sh eet1!F$8<=Prices!E:E,1)))),0))} =SUMPRODUCT(--(Prices!D:D=Sheet1!$F$6),--(Prices!C:C=I7),--(Sheet1!$F$8=Prices!D:D),--(Sheet1!$F$8<=Prices!E:E),Prices!H:H) -- dat842 "dat842" wrote: Another question....are there any limitations that you are aware of when using these formulas? For example, I've put the data table (about 65000 rows) on one tab and the lookup formula in another tab. I also have the data ranges setup to be the entire column on the data tab of the spreadsheet. I can't get the formula to work in this situation. -- dat842 "dat842" wrote: Great! Many thanks. This works. I guess I just had an error when I entered the variables and didn't realize it. Is there a way that I can have it return "unavailable" instead of #N/A if there is an invalid combination of variables entered? -- dat842 "T. Valko" wrote: Here's a small sample file that demonstrates this. I included a second formula. multiple_criteria_lookup_INDEX_MATCH_1.xls 14kb http://cjoint.com/?cygfIVADn7 As you'll see, both formulas return the correct result. If you get #N/A with the array formula that means something isn't matching. There are many possible reasons. You may have unseen whitespace characters in your data. The dates may not be true Excel dates. The numeric values might actually be TEXT numbers. TEXT numbers and numeric numbers are not the same. So, you'll have to troubleshoot for those possibilities. -- Biff Microsoft Excel MVP "dat842" wrote in message ... Thanks. I tried your suggestion, but am still getting a #N/A error. I have entered the formula as an array. Any ideas what I'm missing? -- dat842 "T. Valko" wrote: One way... Array entered** : =INDEX(H2:H10,MATCH(1,IF(D2:D10=A5,IF(E2:E10=3,IF( A9=F2:F10,IF(A9<=G2:G10,1)))),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "dat842" wrote in message ... I'm trying to use the match function with a nested if statement. Is this possible? My table looks like the following. Column D E F G H UNIT TERM STARTDATE ENDDATE RATE 21843 2 2/1/2009 2/7/2009 1350 21843 3 2/1/2009 2/7/2009 1040 21843 4 2/1/2009 2/7/2009 1015 21843 2 2/8/2009 2/14/2009 1350 21843 3 2/8/2009 2/14/2009 1040 21843 4 2/8/2009 2/14/2009 1015 19608 2 3/1/2009 3/7/2009 2055 19608 3 3/8/2009 3/15/2009 1725 19608 4 3/22/2009 3/29/2009 1670 I need the user to enter a value for the unit, term and date and have excel return the matching value in the rate column. The problem is that the date which is entered by the user will be inbetween the StartDate and EndDate values which are in the table. I'm trying to use the match function with a nested if statement to determine if the date is within the StartDate and EndDate range. If it is, the match function should use the date in the StartDate column. For example, User enters a unit in cell A5 = 19608 User enters a date in cell A9 3/10/2009 Term is hard coded as a value 3. Using the table above, I'd like excel to return the value 1725 My formula is =INDEX($H$2:$H$10,MATCH(1,($D$2:$D$10=$A$5)*($E$2: $E$10=3)*($F$2:$F$10 =IF(AND($A$9=F3, $A$9<=G3),0,F3)),0)) However, my formula doesn't work. Any ideas would be appreciated. -- dat842 . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MYSTERY: NESTED INDEX AND MATCH FUNCTIONS | Excel Worksheet Functions | |||
nested MATCH within Array formula? | Excel Worksheet Functions | |||
Nested MATCH with two tables | Excel Worksheet Functions | |||
Nested 'IF', 'LOOKUP', 'AND' or 'INDEX', 'MATCH' | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions |