VLOOKUP from a table in another sheet

[drop05]

Hello, i am a bit new to vlookups. What i am trying to do is have the value in column B, say b1-b10, change depending on the selection in column K so K1-k10

I was trying to use a vlookup and reference a table on another sheet. The sheet will have a column in which Values or key i should say to go into column B and the corresponding value for that key in another column next to it,

what is the best way to set the formula up? Thank you!

keys value
tag1 test 1
tag2 test 2
tag3 test 3
tag4 test 4
tag5 test 5
tag6 test 6
etc etc

so if a user selects in a drop down on k1 selects test 1 then in b1 a value tag1 will appear, if they chose in k1 test 4 then tag4 will come in b1.


thank you in advance

 

[Z51]

If the lookup table is on another sheet, like "Sheet7", then you can use something along the lines of this in B1 and fill down:

=VLOOKUP(K1,Sheet7!A:E,3,0)

This looks for the value in cell K1 on Sheet7 columns A thru E. If an exact match is found in column A, it will return the value from the third column in that table (column C).

 

[drop05]

If the lookup table is on another sheet, like "Sheet7", then you can use something along the lines of this in B1 and fill down:

=VLOOKUP(K1,Sheet7!A:E,3,0)

This looks for the value in cell K1 on Sheet7 columns A thru E. If an exact match is found in column A, it will return the value from the third column in that table (column C).

awesome, i will test this out, yes it is in another sheet so thank you for noting that, at the moment creating the table with data i need

 

[drop05]

If the lookup table is on another sheet, like "Sheet7", then you can use something along the lines of this in B1 and fill down:

=VLOOKUP(K1,Sheet7!A:E,3,0)

This looks for the value in cell K1 on Sheet7 columns A thru E. If an exact match is found in column A, it will return the value from the third column in that table (column C).

Okay i am getting a #na error

so this is my formula

=VLOOKUP(K1,Table1[[Tag]:[Description]],4,FALSE)

this formula is in sheet 1, k1 is on the same sheet

the reference table is in sheet 2 but the table is called table1 and the headers are tag and description
tag is column C and description is column D

there is a drop down selection of description the use can select from and that drop down selects all the selects are in its own row in the table on sheet 2 in column D
the tag column is 1 to 1 for the description so say d2 description goes with c2 tag, d3 to c2 etc
so i am trying to get the corresponding tag to come up in column B1 where the formula is at
but got the #na error

so if the table in sheet 2 is like so
C D

tag description

tag1 test 1
tag2 test 2
tag3 test 3
tag4 test 4
tag5 test 5
tag6 test 6
etc etc

In k1 on sheet 1 if user selects from drop down "test 1" then in B1 where the formula is located it puts "tag1"
In k1 on sheet 1 if user selects from drop down "test 3" then in B1 where the formula is located it puts "tag3"

 

[Z51]

Instead of

=VLOOKUP(K1,Table1[[Tag]:[Description]],4,FALSE)

Try

=VLOOKUP(K1,Table1[[Tag]:[Description]],2,FALSE)

That third parameter in the VLOOKUP function tells the formula what column to retrieve the new value from. In your case, your table is only two columns wide (even though it's in columns C:D), so you lookup the value in the first column, and retrieve the value from the second column.

If an exact match of K1 isn't found in the Tag column, you will get an error, too. If you expect some values to not match, then you could wrap the formula in an IFERROR function, e.g.

=IFERROR(VLOOKUP(K1,Table1[[Tag]:[Description]],2,FALSE),"")

 

[drop05]

Instead of

=VLOOKUP(K1,Table1[[Tag]:[Description]],4,FALSE)

Try

=VLOOKUP(K1,Table1[[Tag]:[Description]],2,FALSE)

That third parameter in the VLOOKUP function tells the formula what column to retrieve the new value from. In your case, your table is only two columns wide (even though it's in columns C:D), so you lookup the value in the first column, and retrieve the value from the second column.

If an exact match of K1 isn't found in the Tag column, you will get an error, too. If you expect some values to not match, then you could wrap the formula in an IFERROR function, e.g.

=IFERROR(VLOOKUP(K1,Table1[[Tag]:[Description]],2,FALSE),"")

I See that makes sense, i changed to that but still got an NA error, but then i realized with how you described it "so you lookup the value in the first column, and retrieve the value from the second column." I am wanting to look up the values in column D so the descriptions and paste the value from column C the tag, so i swapped the places of them and the na error is gone
by chance is there a way to reverse it so i can leave the table as i have it or best just to move the table around

 

[drop05]

Instead of

=VLOOKUP(K1,Table1[[Tag]:[Description]],4,FALSE)

Try

=VLOOKUP(K1,Table1[[Tag]:[Description]],2,FALSE)

That third parameter in the VLOOKUP function tells the formula what column to retrieve the new value from. In your case, your table is only two columns wide (even though it's in columns C:D), so you lookup the value in the first column, and retrieve the value from the second column.

If an exact match of K1 isn't found in the Tag column, you will get an error, too. If you expect some values to not match, then you could wrap the formula in an IFERROR function, e.g.

=IFERROR(VLOOKUP(K1,Table1[[Tag]:[Description]],2,FALSE),"")

curious, is there a way that if k1 is blank, instead of it giving me a #na error, if it is blank then leave the values in b1 blank, with that vlookup

 

[drop05]

curious, is there a way that if k1 is blank, instead of it giving me a #na error, if it is blank then leave the values in b1 blank, with that vlookup

cancel i got it figured out thank you!