Macro for lookup

[WillemB]

Hello,

can someone help me with a macro instead of the Vlookup formula
in the attachment you can find the example excel file

In column A we have a lot of values, these are the look up values.
In column D2:BL66 is the table array.
The values from column A may occur in D2:BL66 (not all in this example), should be linked to the values in column BN

thanks

[nilem]

Hi WillemB,
maybe so

Please Login or Register  to view this content.

[Kaper]

or without tables (probably somewhat slower)

Please Login or Register  to view this content.

edit: I was wrong, a bit quicker, but the difference is pretty small.

[nilem]

as an option

Please Login or Register  to view this content.

[WillemB]

Thanks Nilem,

Is it also possible if the value in column BN is a text like (example FRGTESAF

[WillemB]

Hoi Nilem,

Just run your second macro, it works great!

Thanks!!

[WillemB]

Nilem,

short question,
is it possible to run the macro on an other sheet than where the table array is?

[nilem]

something like this

Please Login or Register  to view this content.

[Arkadi]

WillemB,
What do you mean? Put the results on a different sheet than where the lookup table is? Easy, just need to reference the correct sheet in the code.

[WillemB]

I did, but nothing happens,

just have the table array on sheet 1
and the look up on sheet 2 column B
have to fill in the linked value to sheet 2 column D

[nilem]

Can you attach an example of your file?

[WillemB]

here the exampl2

[nilem]

try

Please Login or Register  to view this content.

[Kaper]

I added references to sheets and corrected ranges and these were only changes needed to previous macro:

Please Login or Register  to view this content.

[WillemB]

Hoi Nilem,
It's working Thanks!!