find a value in a table based on 2 different criteria

[Picman]

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.

 

[~L]

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]

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]

=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]

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?

 

[Pete_UK]

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

 

[Picman]

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))

 

[~L]

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]

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]

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]

It looks as though the extra characters were the issue, thank you very much.
I should have seen that myself.

 

[~L]

The thing about spaces is... there's nothing to see.

Glad I could help!

 

[Picman]

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]

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('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]

I'm running 2003 will this still work?

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('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)))

 

[~L]

Yes (providing you replace ErrorValueHere with something valid).

 

[Picman]

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]

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.