Need a Formula for CLOSEST MATCH

I have two lists.  LIST A & LIST B.

I want to compare codes to LIST A from LIST B.  When I do VLOOKUP all the exact match ones do come up but the ones that are closest do not.  Example LIST A has 919401-919409 so the closest match in LIST B is 91940 for all the codes 919401-91409 from LIST A, but VLOOKUP does not show that.  I have tried Index/Match but still not luck.  Is there a formula you can tell me or write it for me please.  It is really urgent and required. 

Help will be very appreciated.

Thank You

LIST A (Column A) LIST B (Column b)
919196 919197
919197 919196
919401 9194
919402 91940
919403 9198

Answer
Answer

Hi,

You may refer to my solution at this link - http://www.ashishmathur.com/performing-an-iterative-lookup-to-return-closest-match/

I just tried my solution on your dataset and it works just fine.

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated October 5, 2021 Views 237 Applies to: