Index & Match query

Hi All,

i've been struggling with this for a while now and have conceeded defeat. Here's the problem in a nut shell.

I have a Pivot table listing months across the top and makes of vehicle down the side.


MAKE Feb Mar

ALFA
AUDI 2
BMW 4
CHRY
CITR 1



Staff have the option on a seperate sheet to select a date and a make and it should return a series of values (there are about 12 tables in total) on a form.
The trouble is it is returning incorrect values or N/A. Here is how i'm trying to do it.

FORMULA.

=INDEX('PIVOT DATA'!A42:O76,MATCH(B25,'PIVOT DATA'!A42:O42,0),MATCH(B26,'PIVOT DATA'!A42:A76,0))

Any help would be great.

Thanks.

Hi, try this:

=INDEX('PIVOT DATA'!B43:O76,MATCH(B26,'PIVOT DATA'!A42:A76,0),MATCH(B25,'PIVOT DATA'!A42:O42,0))

do not use row and column headers in your search table:

B43:O76 instead of A42:O76


Hope this helps!

Jean-Guy

thanks for the attempt but unfortunatly that doesn't resolve the problem. Is there just a problem using index & match in Pivot Tables?

I need to include column A as that's the column that has the makes listed in it. Tell me if i'm missing something here. But i thought to look something up you had to have a matching value.

Hi,

The formula I gave you should have worked. Maybe it's a format problem, one set of dates could be simple text and the other actual dates formatted as months. Check that out and it that is not the case then maybe you could post a sample sheet to look at. I'm not an expert but I'm sure someone in here can help you.

Regards
Jean-Guy

Can you give an example of what you're putting in B25 and B26, is one formatted as a date?

Good morning,

i can send an example through if i ever work out how to attch a spread sheet to a post.