So I am trying to lookup a specific value in a very large spreadsheet (>100,000 rows). A sample spreadsheet is attached for reference.
I am looking to return the value in the VALUE column. Usually this is a number but it could be a text string.
There are 3 conditions a user sets (in the yellow highlighted cells) in order to find the correct VALUE. The first 2 are "Customer ID" and "Metric". These 2 should always produce a perfect match within the dataset.
The last variable is the "Date of New Shipping Process". This is related to the "Shipping Date" but there will likely NOT be a direct match within the data set. I am interested in finding the "Shipping Date" that is CLOSEST to the "Date of New Shipping Process", regardless of whether it is before or after that date.
Once the nearest specific shipping date has been identified that also matches the designated Customer ID and Metric, I am interested in returning the matching VALUE in column C.
I have tried doing this nesting INDEX/MATCH array functions (eg Index(Range,Match(1,((A:A=Customer ID)*(B:B=Metric))...,0))) but I don't know how to find the CLOSEST shipping date from there. I've also tried using a Match(min(abs(range-"Date of New Shipping Process"))) theme to no avail.
Any ideas would be MUCH appreciated...
Thanks!
Bookmarks