| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find a value in a table based on 2 different criteria
I need to look up a value in a table based on 2 different criteria. The first
is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
This can be done using INDEX with MATCH.
In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
This didn't work for me, I received an error. This is the formula after I
adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
Still not working for me. Let me varify that I know what the formula is
doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
Are your entries in B1:Y1 proper dates, or are they text values? What
value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45*pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: *=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. *I would also make the whole lookup range absolute: *=INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) *(formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code * * * * * *Feb 09 * * *Mar 09 *Apr 09 0050B * * * * * * * * * * *4 * * * *5 * * * 2 1008-3011-00-000 * * 0 * * *6 * * * 0 1008-3041-00-000 * * 0 * * *24 * * *0 1008-4101-00-000 * * 0 * * *24 * * *0 Table of Data: ITEM * * * * * * * * * * * *JAN 09 *FEB 09 *MAR 09 *APR 09 0050B * * * * * * * * * * * 6 * * * 4 * * * 5 * * * 2 1008-3011-00-000 * * * * * *0 * * * 6 * * * 0 * * * 0 1008-3041-00-000 * * * * * *0 * * * 24 * * *0 * * * 0 1008-4101-00-000 * * * * * *0 * * * 24 * * *0 * * * 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
I've tried it all ways possible. Originally F2 was a formula and the column
headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
Looks like that ,0, before the first match was still in it (where the #N/A is
now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
What i showed was the error evaluation not the actual formula which does not
contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
If the data has been exported from some source (especially the web), check
for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
It looks as though the extra characters were the issue, thank you very much.
I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
The thing about spaces is... there's nothing to see.
Glad I could help! "Picman" wrote: It looks as though the extra characters were the issue, thank you very much. I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
One last question, how can i make the formula return a "0" or something else
instead of "#N/A" when it doesn't find a match? "~L" wrote: The thing about spaces is... there's nothing to see. Glad I could help! "Picman" wrote: It looks as though the extra characters were the issue, thank you very much. I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
If you have 2007 or don't mind creating a UDF, you can use IFERROR.
Otherwise you can add the values that are producing N/A to your table and give them a value of 0 (or whatever you prefer). Otherwise you can use: =IF(ISNA(INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))),ErrorValueHere,INDEX('CUSTOM ER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))) "Picman" wrote: One last question, how can i make the formula return a "0" or something else instead of "#N/A" when it doesn't find a match? "~L" wrote: The thing about spaces is... there's nothing to see. Glad I could help! "Picman" wrote: It looks as though the extra characters were the issue, thank you very much. I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
I'm running 2003 will this still work?
"~L" wrote: If you have 2007 or don't mind creating a UDF, you can use IFERROR. Otherwise you can add the values that are producing N/A to your table and give them a value of 0 (or whatever you prefer). Otherwise you can use: =IF(ISNA(INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))),ErrorValueHere,INDEX('CUSTOM ER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))) "Picman" wrote: One last question, how can i make the formula return a "0" or something else instead of "#N/A" when it doesn't find a match? "~L" wrote: The thing about spaces is... there's nothing to see. Glad I could help! "Picman" wrote: It looks as though the extra characters were the issue, thank you very much. I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
Yes (providing you replace ErrorValueHere with something valid).
"Picman" wrote: I'm running 2003 will this still work? "~L" wrote: If you have 2007 or don't mind creating a UDF, you can use IFERROR. Otherwise you can add the values that are producing N/A to your table and give them a value of 0 (or whatever you prefer). Otherwise you can use: =IF(ISNA(INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))),ErrorValueHere,INDEX('CUSTOM ER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))) "Picman" wrote: One last question, how can i make the formula return a "0" or something else instead of "#N/A" when it doesn't find a match? "~L" wrote: The thing about spaces is... there's nothing to see. Glad I could help! "Picman" wrote: It looks as though the extra characters were the issue, thank you very much. I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
The problem is that the value that i am trying to match (A3) may not appear
in the indexed table at all. There may not be a row of data for the match function to match on and it is about 1000 rows tall so entering the missing values would be impractical. "~L" wrote: Yes (providing you replace ErrorValueHere with something valid). "Picman" wrote: I'm running 2003 will this still work? "~L" wrote: If you have 2007 or don't mind creating a UDF, you can use IFERROR. Otherwise you can add the values that are producing N/A to your table and give them a value of 0 (or whatever you prefer). Otherwise you can use: =IF(ISNA(INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))),ErrorValueHere,INDEX('CUSTOM ER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))) "Picman" wrote: One last question, how can i make the formula return a "0" or something else instead of "#N/A" when it doesn't find a match? "~L" wrote: The thing about spaces is... there's nothing to see. Glad I could help! "Picman" wrote: It looks as though the extra characters were the issue, thank you very much. I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find a value in a table based on 2 different criteria
The formula
=IF(ISNA(INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))),0,INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))) may be your best bet then. "Picman" wrote: The problem is that the value that i am trying to match (A3) may not appear in the indexed table at all. There may not be a row of data for the match function to match on and it is about 1000 rows tall so entering the missing values would be impractical. "~L" wrote: Yes (providing you replace ErrorValueHere with something valid). "Picman" wrote: I'm running 2003 will this still work? "~L" wrote: If you have 2007 or don't mind creating a UDF, you can use IFERROR. Otherwise you can add the values that are producing N/A to your table and give them a value of 0 (or whatever you prefer). Otherwise you can use: =IF(ISNA(INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))),ErrorValueHere,INDEX('CUSTOM ER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))) "Picman" wrote: One last question, how can i make the formula return a "0" or something else instead of "#N/A" when it doesn't find a match? "~L" wrote: The thing about spaces is... there's nothing to see. Glad I could help! "Picman" wrote: It looks as though the extra characters were the issue, thank you very much. I should have seen that myself. "~L" wrote: If the data has been exported from some source (especially the web), check for spaces after the text. Spaces or other non-printing space-like characters are often the culprit. If the match items are numbers, make sure to check up to 4 or 5 decimal places to make sure the number you see is actually the number you see. If you are comparing numbers stored as text to numbers, convert the numbers stored as text to actual numbers. An easy way to do this is to copy a blank cell, highlight the range of text-numbers, then paste special-subtract. "Picman" wrote: What i showed was the error evaluation not the actual formula which does not contain the 0 after the comma in the index function. This is the actual formula: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) When I use the troubleshooting method you suggested it is both MATCH augments that are returning #N/A. "~L" wrote: Looks like that ,0, before the first match was still in it (where the #N/A is now). =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) The general form of index is: INDEX(Array, row number, column number, area number) where the area number is optional For array you have 'CUSTOMER ORDER'!$A$1:$Y$371 For row number you have 0 (not a valid selection, which is why it returns NA) For column number you have MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0) and the second match is the area number, but your array has only one area, so this is optional (or if it isn't 1 it causes an error). Another troubleshooting method is to click in the formula bar, placing the cursor inside the parentheses for the function you want to examine, then click the fx button to the left of the formula bar. If you change the position of the cursor from one function to another, it will tell you the result of that function at the bottom of the window that pops up. If you do this on the Index and both matches, you can tell us what function is producing an error (assuming correcting the INDEX doesn't solve this). "Picman" wrote: I've tried it all ways possible. Originally F2 was a formula and the column headings were text. I've tried to make them both actual dates, then input the 1st of the month for both, then both text etc. I did this thinking that it must be were the problem is. I used error checking but the error showed up at the end of the index after the comma, if that helps. This is how the evaluation read: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,#N/A,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Pete_UK" wrote: Are your entries in B1:Y1 proper dates, or are they text values? What value do you have in F2 to specify the month in question? Pete On Feb 18, 4:45 pm, Picman wrote: Still not working for me. Let me varify that I know what the formula is doing. The "Index" section is indexing the data table, the 1st "Match" is finding the appropriate row to look in, and the 2nd "Match" if finding the column to look in. Am I correct? "~L" wrote: =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) has an extra ,0, before the first MATCH. I would also make the whole lookup range absolute: =INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "Picman" wrote: This didn't work for me, I received an error. This is the formula after I adjusted it to my parameters. =INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0)) "CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25 column wide by 371 rows tall table with the first row containing column headers. On my original submission I did not show all of the columns of my results worksheet because of space issues. "A3" contains the value to search for on the row of the data table and "F2" contains the reference value to determine the column. "~L" wrote: This can be done using INDEX with MATCH. In your results table on cell B2: =INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1Â*,0)) As long as the dates at the tops of the columns are actually dates in B1 you can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy"). C1 would be =DATE(year(today()),month(today())+1,1) D1 would be =DATE(year(today()),month(today())+2,1) Otherwise, if they are just text, wrap that in a TEXT Function like: =TEXT(DATE(year(today()),month(today()),1),"mmm yy") Is that what you were hoping for? Just make sure if the dates are dates that you use the first of the month in all cases, or there won't be a match. "Picman" wrote: I need to look up a value in a table based on 2 different criteria. The first is the item code, and the second is the month. The "B", "C" and "D" column headers are the month and the value that determines what column of the table to look in, the table is laid out in a 24 month grid. The (month) value at the top of the column "B" will change to the current month, every month and subsequent columns will change accordingly. Results Worksheet: Product Code Feb 09 Mar 09 Apr 09 0050B 4 5 2 1008-3011-00-000 0 6 0 1008-3041-00-000 0 24 0 1008-4101-00-000 0 24 0 Table of Data: ITEM JAN 09 FEB 09 MAR 09 APR 09 0050B 6 4 5 2 1008-3011-00-000 0 6 0 0 1008-3041-00-000 0 24 0 0 1008-4101-00-000 0 24 0 0 Ultimately I'm trying to develop a rolling 12 month report starting with the current month and moving out from there. The current month will change as time progresses. The data is supplied from a table containing 24 months of data.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
| Where is "open/tools/find/find files that match these criteria"? | Excel Discussion (Misc queries) | |||
| sum based on 2 criteria within a table | Excel Worksheet Functions | |||
| summing values in a data table based on criteria in another column | Excel Worksheet Functions | |||
| find minimum of range based on multiple criteria | Excel Worksheet Functions | |||
Linear Mode
