Array lookup nearest match
Posted on 2014-12-31
I need to do a lookup on two columns, "Total amount" with an exact match, and "Payment date" whichever is the closest match, and then return the values for "Payment date", "Cost centre" and "Supplier name"
Example xls attached, the lookup table is sheet "lookupdata" and example of the search result in sheet "search"
Purpose is to assist in manually reconciling transactions between two lists.
I'm guessing this is easiest solved as an array formula, but my memory and skills here are very rusty. Back in the day i'd have it knocked out in a jiffy but alas... please help!