• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX/MATCH for approximate date

M

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
 
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

  • PROXIMITY DATE FORMULA.xlsx
    39.8 KB · Views: 8
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.
 
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.
 
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

  • PROXIMITY DATE solution.xlsx
    35.8 KB · Views: 15
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.
 
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!!
 
You must log in or register to reply here.
Back
Top