Match Formula

[Patcheen]

im using this formula to match dates but its not matching exactly its out by a few days

=IFERROR("A "&MATCH(B4,'Shirt Database'!$G$2:$G$60000,0),"")

b4 is the date im trying to match in 'Shirt Database'!$G$2:$G$60000 but as i say its not doing so its out by few days example b4 could be any date so im gonna use 04/05/91 but it returns 24/3/91 as i say it is only out by few days

 

[Marcelo Branco]

I don't understand what you are trying to do.
The MATCH function returns the relative position of the lookup_value.
So, for example, if B4 (04/05/91) is found in G3 - the second cell of the range 'Shirt Database'!G2:G60000 - it returns 2, never a date.

M.

 

[Patcheen]

G in the 'Shirt Database'!$G$2:$G$60000 is where the dates are - ur right im trying to find the location of where the date im looking for is in the Shirt Database sheet

 

[EdNerd]

What happens if you just enter a MATCH expression?
=MATCH(B4,'Shirt Database'!$G$2:$G$60000,0)
Do you get the right number?

Are your dates all entered in the same format? All are numbers - none are text values?
Do you have a filter or hidden rows on Shirt Database?

 

[Patcheen]

yes you are correct it gives me the location of where it should be but it gives me the location that is a few days away from the date im looking for
example date 24/3/91 is in G1000 in the 'Shirt Database sheet but it returns the location G996 instead of location G1000

 

[Patcheen]

What happens if you just enter a MATCH expression?
=MATCH(B4,'Shirt Database'!$G$2:$G$60000,0)
Do you get the right number?

Are your dates all entered in the same format? All are numbers - none are text values?
Do you have a filter or hidden rows on Shirt Database?

yes the dates are formatted as dates, no text value no hidden rows or filters,

your formula gets me closer it get me to 1 row away from the perfect result

 

[EdNerd]

I'm not following this too well.
You said in your first reply:

example date 24/3/91 is in G1000 in the 'Shirt Database sheet but it returns the location G996 instead of location G1000

In your second reply, you said:

your formula gets me closer it get me to 1 row away from the perfect result

Are you 1 row or 4 rows away??
Are all your dates in G2:G60000 sorted in ascending or descending order?
Have you tried it with different dates? Are you consistently 1 (or 4) rows off for each date?

 

[Patcheen]

1 row away... the dates are in accending order up to present date. yes tried with diff dates

 

[EdNerd]

If you are consistently just 1 row away, then you might have an incorrect range in your formula.
Or you can just add or subtract 1 from the result to get where you need.
=MATCH(B4,'Shirt Database'!$G$2:$G$60000,0)+1

 

[Patcheen]

that gives me the date an not the location but thank you