excel vba vlookup multiple values (1 Viewer)

[Futurz]

Hi, I am looking for a simple vba for excel, to match a vlookup values mentioned in column 'E' and display the result in column 'F'
Given Index in Column 'B' and value in Column 'A'
Note : The attached file is a sample, but the actual data may be upto 100 rows...or more

 

[pbaldy]

There is no attachment. If memory serves, the index column must be to the left of the value column in a VLookup, though it's been a while.

 

[Futurz]

Hi, Pbaldy,
I have reattached my xls file.

 

[pbaldy]

This would appear to confirm my recollection that the index has to be to the left of the value, not the other way around:

VLOOKUP function - Microsoft Support

Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient.

support.microsoft.com

 

[Gasman]

Use index()and Match() instead.

 

[Futurz]

This would appear to confirm my recollection that the index has to be to the left of the value, not the other way around:

VLOOKUP function - Microsoft Support

Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient.

support.microsoft.com

Dear Pbaldy,
It doesn't matter if we shift "index" to the left of "value". I just need the result as mentioned.
Could you please set the above attached file to get the desired "result" (through a vba code)
Thanks.

 

[pbaldy]

Hopefully Gasman's suggestion works for you. I'm not that strong in Excel, I'd probably create a custom function or something.

 

[Gasman]

Example 5 is your best bet.

VLOOKUP In VBA – With Examples

In this post we will understand how to use & apply the VLOOKUP in VBA. I have written this post assuming that you have basic knowledge of VLOOKUP Function.

www.exceltrick.com

However Vlookup only handles a single value in a cell, not a string of values, as you have in E5 ?

 

[Isaac]

Dear Pbaldy,
It doesn't matter if we shift "index" to the left of "value". I just need the result as mentioned.
Could you please set the above attached file to get the desired "result" (through a vba code)
Thanks.

use vlookup.

 

[pbaldy]

use vlookup.

Isaac, the problem is the multiple values in a cell. Do you know a way to make VLookup() handle that?

 

[Gasman]

Isaac, the problem is the multiple values in a cell. Do you know a way to make VLookup() handle that?

How is that meant to work anyway?, which value do you want to search for?
All of them?, so each subsequent one overwrites the value of the previous search?

 

[pbaldy]

I'm not the OP, but from the attachment each value in the lookup string gets its corresponding value returned in the return string. Concatenated lookup, concatenated result.

 

[Futurz]

can anyone else provide me a vba code..?

 

[Gasman]

Not me, sorry

 

[arnelgp]

here is a VBA alternative.

 

[Futurz]

here is a VBA alternative.

Dear Arnelgp,
I thank you once again for your brilliant efforts.
My query is Solved
Thanks...

 

[Futurz]

Dear Arnelgp,
I thank you once again for your brilliant efforts.
My query is Solved
Thanks...

Dear Arnelgp,
There is a little inaccuracy in the result when I modified the index & value data..
Please have a look at the attached file.
Thanks

 

[arnelgp]

see the change on calling the function.

 

[Futurz]

see the change on calling the function.

Ok, It is Solved, Thanks a lot...

 

[Isaac]

Isaac, the problem is the multiple values in a cell. Do you know a way to make VLookup() handle that?

Oh -
Sorry, I don't usually download people's Excel attachments, as I tend to help those who will describe the problem in writing.

Is it like multiple lines separated by line breaks? Some left, right, find, and char() might work? Increase complexity to find 2nd, 3rd, 4th lines etc.
Same thing if we're talking about the returned value, I guess you'd have to vlookup the whole thing, then start wrapping it in more formulas to chunk it out.

If I still have no idea what is needed, feel free to ignore me