VLOOKUP Advance question. Need Help.

[imshivam]

1. User inputs two comma separated lists - list A and list B.
2. We take an element from the list A - and match it with the entire range.
3. Whichever cell from list B has the highest match, will be return against the values of List A.

Can anyone try to solve this, I tried to use VLOOKUP with different set of functions but it didn't resolved this query.

 

[p45cal]

Maybe a Power Query Fuzzy join?:

 

[imshivam]

Maybe a Power Query Fuzzy join?:
View attachment 80711

But this won't work with all the test cases.

 

[p45cal]

But this won't work with all the test cases.

updated image in message#2. Is that better?

 

[imshivam]

updated image in message#2. Is that better?

This seems to be working. Can you please share the formula/function. I would like to use it on other cases.

 

[p45cal]

Tell you what, you supply a file with this stuff in it and I'll return it with added value?

 

[imshivam]

I was thinking of creating some other test cases. Also I want to know if it is better to sort the list alphabetically before apply the function? What do you think?

 

[p45cal]

Also I want to know if it is better to sort the list alphabetically before apply the function?

I doubt it, but it might be worth testing.

 

[imshivam]

I was thinking of creating some other test cases. Also I want to know if it is better to sort the list alphabetically before apply the function? What do you think?

What will be the output for the fourth case

 

[bosco_yip]

In refer to the forum rule regarding "Upload a Sample File", please don't attach a link of your file in your reply :

Please upload a Sample File using the "Upload a File" button at the bottom of the page.

And read the following forum rule for your necessary action
Site Rules - New Users - Please Read | Chandoo.org Excel Forums - Become Awesome in Excel

Regards

 

[p45cal]

I doubt it, but it might be worth testing.

No difference in limited testing:


(Also shows output for the 4th case)

 

[p45cal]

I missed your file.