Match & Index issue with dates

Hi,

hoping someone can help me, I've been slowly learning match & index from a few different sources, starting to come together but when I use dates as one of the lookups I'm having problems, generally returning #N/A. The first time I do it it works, then if I change the lookup criteria to another date it fails.

I've attached a working example here, if you change any of the country names the formula works fine, if you type over the date criteria (G2) it returns #N/A but if you paste one of the lookup dates from column A into g2 it does work.

Any ideas!? Is it a formatting issue/is it possible to do what I want?

Any help is greatly appreciated I've been trying to figure it out for a while!

Thanks.

index_match_test.xlsx

Hi Fotis,

I did, great spot thanks! I'll try and make sure the dates are the same from both.

Thanks again for your help

Hi newdoverman, that's brilliant! So the 1 is the data type which is multiples by?

Works really well thanks, unfortunately the original data is in a pivot from an external source so I can't change the type (unless I'm missing something here but can't see the option?), I'm having to build a new table in a new sheet which just says =text(c1(where pivot data is),"MM/DD/YYY") to force my new date in the new sheet to become text. It's the only way I can lookup.

seems a bit convoluted though just because I can't change the data type in my pivot! Really good to know that forces the date out of it though.

Hmm I'm getting "can't move part of a PivotTable" when I try it, as if it is actually trying to split the text. I tried it both delimited and fixed width..

OK, really appreciate the help though. I'll go with the long way for the time being! Will try and speak to the DB owners and see if the data format the data comes in on can be changed.

thanks again.

Hi, someone here just suggested a workaround solution of converting the date next to the pivot table, so just having =DATE(RIGHT(N84,4),MID(N84,4,2),LEFT(N84,2)) in a new column the the left of the pivot then I can use my index & match from that. Haven't tried yet but hopefully sounds like a better solution than the way I had it.

As new data enters the pivot each day the pivot should just pick it up.

Hopefully!