Finding the nearest match without reusing results
Hello Bernd,
Glad to see someone else offering solutions! I think your work is not
quite done, however, if you want to take into account the OP's goal of
not repeating a match. Not sure whether easier to implement in your
formulas below or your combined formula/VBA solution.
- David
On Sep 15, 6:12 am, Bernd P wrote:
Hello,
With two helper columns you can get a comparibly fast worksheet
solution:
Store State Sales Min Diff Index Closest Store Sales of closest
2 MO 2120 =MIN(IF(Sheet3!$B$2:$B$20=B2,ABS(Sheet3!$C$2:$C$20-C2)))
=MATCH(B2&","&D2,Sheet3!$B$2:$B$20&","&ABS(Sheet3! $C$2:$C$20-C2),)
=INDEX(Sheet3!$A$2:$A$20,E2) =INDEX(Sheet3!$C$2:$C$20,E2)
MIN and MATCH formulas have to be array-entered.
Regards,
Bernd
|