Hi All
My brain is struggling to wrap itself around index and matching with 2 criteria where one criteria is exact match and the other is less than
My dataset (apologies its not a mini sheet, work computer restricting the download)
What I want to achieve is to match column A to exactly "800101" then match Column C to nearest date to <Today and return the corresponding value from column B.
Iv tried using this formula, but it only returns the B column value that is closest to today and doesn't factor in the match for 800101 in column A
I was then hoping to use VBA to replicate the index and match using worksheetfunctions
I would really appreciate Any advice you can give me.
My brain is struggling to wrap itself around index and matching with 2 criteria where one criteria is exact match and the other is less than
My dataset (apologies its not a mini sheet, work computer restricting the download)
A | B | C | |
800101 | X1 |
| |
800102 | X1 |
| |
800111 | X1 | 05/08/2020 | |
800101 | X2 | 01/09/2020 | |
800110 | X1 | 05/05/2020 | |
800106 | X3 | 11/12/2020 |
What I want to achieve is to match column A to exactly "800101" then match Column C to nearest date to <Today and return the corresponding value from column B.
Iv tried using this formula, but it only returns the B column value that is closest to today and doesn't factor in the match for 800101 in column A
Excel Formula:
=INDEX(B2:B20, MATCH(TODAY(),C2:C20, 1),MATCH(800101,A2:A20,0))
I was then hoping to use VBA to replicate the index and match using worksheetfunctions
I would really appreciate Any advice you can give me.