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.

BINGO!

Here is an exampe of what i was trying to explain, it edited down but you should get the right idea. Thanks for any help you can offer.

Hi

As I suspected you were trying to match text "G6" with actual dates formatted as months "PIVOT DATA!B5:L5."

try this:

=INDEX('PIVOT DATA'!B7:M37,MATCH(G5,'PIVOT DATA'!A7:A37,0),MATCH(G20,'PIVOT DATA'!B5:M5,0))

also I would change B51 and B64 to "Grand Total" to get a match when "All" is selected in G6 or G5

Hope this helps!

Regards
Jean-Guy

Cheers Guys,

That was causing me all sorts of problems.


Thanks Again.