# INDEX/MATCH for approximate date

#### Marco.Zingariello

##### New Member
Hi Everyone,

I'm trying to use an index match formula to find an approximate date but it's not working properly any help would be greatly appreciated.

I have 2 sheets, Sheet 1 and Sheet 2. Each sheet has a list of names in Column A with a trim formula, and a list of dates in column K. I'm trying to match the name in sheet 1 with the name in sheet 2, and the date in sheet 1 in column K with the closest date in column K in sheet 2 with the matching name, but its not working.

=IF(ISERROR(INDEX('SHEET2'!K3:K37000,MATCH('SHEET1'!A3&'SHEET1'!K3,INDEX('SHEET2'!A3:A37000&'SHEET2'!K3:K37000,1),1))),"",INDEX('SHEET2'!K3:K37000,MATCH('SHEET1'!A3&'SHEET1'!K3,INDEX('SHEET2'!A3:A37000&'SHEET2'!K3:K37000,1),1)))

It is giving me dates but they don't correspond to the name I'm trying to match it too.

Thanks

#### Luke M

##### Excel Ninja
Staff member
Can you upload a workbook example?

#### Marco.Zingariello

##### New Member
Here's a sample file. You see that some of thats are correct and some aren't. When I made the formula with absolute values I got only a single date for all of them.

#### Attachments

• 39.8 KB Views: 6

#### Luke M

##### Excel Ninja
Staff member
Wonderful, thanks.
Formula in M3:
=INDEX(INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)):INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)+COUNTIF(Sheet2!A:A,A3)),MATCH(K3,INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)):INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)+COUNTIF(Sheet2!A:A,A3)),-1))
Where formula displayed #n/a is when input date is later than any date in data. If you want to supress the errors, wrap the above in a IFERROR function like
=IFERROR(INDEX(INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)):INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)+COUNTIF(Sheet2!A:A,A3)),MATCH(K3,INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)):INDEX(Sheet2!K:K,MATCH(A3,Sheet2!A:A,0)+COUNTIF(Sheet2!A:A,A3)),-1)),"")

Assumes that data in Sheet is grouped by employees, and dates are in descending order.

#### Marco.Zingariello

##### New Member
I tried the formula but no joy. Kept getting an error for too many arguments on the ,-1)) portion. I removed the :'s next to the INDEX, I never seen that in an index match formula. Either way thanks for your help. I'll keep trying.

#### Luke M

##### Excel Ninja
Staff member
We want to look at a dynamic range. With the MATCH and COUNTIF functions, we can find the First cell (via INDEX) and the last cell (via INDEX). Just like we say "the range of cells between A1 and A10 is shown as A1:A10" we can do the same with our INDEX functions, since INDEX returns a range.

Attached file has formula included.

#### Attachments

• 35.8 KB Views: 8

#### Marco.Zingariello

##### New Member
Interesting it worked in the sample but in the master workbook.

#### Haseeb A

##### Active Member
Hello Marco, try this Array Formula

In M3, enter with CTRL+SHIFT+ENTER,

=IFERROR(LARGE(IF((Sheet2!A\$3:A\$428=A3)*(Sheet2!K\$3:K\$428<=K3),Sheet2!K\$3:K\$428),1),"N/A")

Then copy down.

#### Marco.Zingariello

##### New Member
Thanks now it works!!! I appreciate the help!!

#### Marco.Zingariello

##### New Member
I'll give that LARGE formula a try!!!

#### sgmpatnaik

##### Active Member
@Marco.Zingariello

Please change the Range type in your master book as K:K to K2:K1000 etc

hope it will give some thing other wise inform us

Thanks

#### Marco.Zingariello

##### New Member
I've tried both formula's and they work well but not completely exact. I've also built this formula but it only gave me the earliest date for each person in column A
=INDEX('SHHET2'!K3:K40000,MATCH(A3,'SHEET2!A3:A40000,(MATCH(MIN(ABS('SHEET'!K3:K40000-K3)),ABS('SHEET2'!K3:K40000-K3),0))))
also tried another one I built but this one didnt extract the closest dates either, it only occasionally worked.
=INDEX('SHEET2'!K3:K40000,MATCH(A3&K3,INDEX('SHEET2'!A3:A40000&'SHEET2'!K3:K40000,1)))

Hmm I'll keeping working on it!!