Help with VLOOKUP macro

[Kanylen]

Hi All

I hope someone can help me design a small VLOOKUP macro:

In sheet 1 - col A - I have a list of drop-down selections, these selections originates from a list in sheet 2 (col A) and consists of the following:
A B
1 IceSeed 1
2 IceRod 2
3 IceSphere 3
4 Other 4
5 Combination 5
6 Unknown 9

What I need is at macro that, when I in Sheet1-ColA makes a selection from the drop down list ... will return the corresponding value from Sheet2-col B ..... and put the value in Sheet1-ColB giving this:

A B
1 IceRod 2
2 Other 4
3 Unknown 9
4 IceSeed 1

/Cheers, tommy

 

[Luke M]

Welcome to the forum, Kanylen!

In Sheet1, B1, put this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,FALSE))
Copy down as needed.

 

[Kanylen]

Hi Luke, thanks

I did get the VLOOKUP function up and running, but the problem with the copy/paste method is that it changes/increases the values in the lookup function when you paste it .... thus I will need to go back at change the function every time. That was you I thought a macro might do the job ;o)

 

[Luke M]

Can you explain what you mean by "changes the values in the lookup function"?

 

[Kanylen]

Luke, just trying your function .. maybe it will work as I did it slightly different ... but what will you put between "" ?? The different text-strings from the drop-down list ?

 

[Kanylen]

It changed it slightly to this
=VLOOKUP(A:A;Sheet2!A:B;2;FALSE)
Now it does the job an I can just copy/paste it ;o)

thanks a lot ;o)

 

[Luke M]

You wouldn't do anything in the "". It's checking if the cell is blank, and if so, do nothing. If it's not blank, do the VLOOKUP. Glad you got the formula working...even though referencing the entire column is an odd thing to do.

 

[Kanylen]

Hmmm .... see your point, but when I try to put in the if-function I get this ??

 

[Luke M]

Looks like your regional settings uses semicolons instead of commas to separate arguments. I will make the adjustment.
=IF(A1="";"";VLOOKUP(A1;Sheet2!A:B;2;FALSE))

 

[Kanylen]

Now it works like a dream, and doesn't ref the entire col ;o)

Thank a lot ;o)