Excel: A job for vlookup?
July 10, 2012 1:20 PM Subscribe
Excel help? I think this is a job for vlookup but I'm not sure.
I have this table in Worksheet 1:
Apple...............Red...............Crunchy
Banana............Long..............Soft
Grape..............Small..............Juicy
Watermelon......Huge..............Superjuicy
I have a second table in Worksheet 2:
Apple...............Red...............[ C1 ]
Banana............Long..............[ C2 ]
Grape..............Small..............[ C3 ]
Watermelon......Huge................[ C4 ]
In Worksheet 2, what is the formula I would enter in C1 that will go look at Worksheet 1, find every row with the word Apple in the first column, and fill in the word that is two columns to the right of Apple? Big thanks in advance for any help!
I have this table in Worksheet 1:
Apple...............Red...............Crunchy
Banana............Long..............Soft
Grape..............Small..............Juicy
Watermelon......Huge..............Superjuicy
I have a second table in Worksheet 2:
Apple...............Red...............[ C1 ]
Banana............Long..............[ C2 ]
Grape..............Small..............[ C3 ]
Watermelon......Huge................[ C4 ]
In Worksheet 2, what is the formula I would enter in C1 that will go look at Worksheet 1, find every row with the word Apple in the first column, and fill in the word that is two columns to the right of Apple? Big thanks in advance for any help!
Response by poster: Oh, and the formula has to be something I can drag down to fill in C2, C3, C4, etc. It can't say, "go look for "Apple" in A1. It has to say, "Go look for the contents of A1".
posted by stupidsexyFlanders at 1:27 PM on July 10, 2012
posted by stupidsexyFlanders at 1:27 PM on July 10, 2012
=VLOOKUP(A1,Worksheet1!A:C,3,0)
posted by Perplexity at 1:27 PM on July 10, 2012 [1 favorite]
posted by Perplexity at 1:27 PM on July 10, 2012 [1 favorite]
i'm too lazy to double check it but if i remember correctly it should be =vlookup(select cells a1-a4,2) 2 is to tell it how many columns over from the column of fruit list it should extract the value from.
this will return whatever the value is next to the first instance of apple, if it is different in the 2nd instance it won't tell you.
posted by saraindc at 1:27 PM on July 10, 2012
this will return whatever the value is next to the first instance of apple, if it is different in the 2nd instance it won't tell you.
posted by saraindc at 1:27 PM on July 10, 2012
vlookup will only work if the list is in alphabetical order. You need index/match.
posted by carmicha at 1:30 PM on July 10, 2012 [1 favorite]
posted by carmicha at 1:30 PM on July 10, 2012 [1 favorite]
=VLOOKUP(A1,Sheet1!$A:$C,3,FALSE)
Or what Perplexity said.
posted by Oddly at 1:30 PM on July 10, 2012
Or what Perplexity said.
posted by Oddly at 1:30 PM on July 10, 2012
What Perplexity said.
A1 = value to be searched for
Worksheet1!A:C = search range; vlookup searches in the first column only, so this means search in column A on worksheet 1.
3 = the column in the search range to take the return value from; so column A = 1, B=2, and C (the one you want the return value from) = 3
0 = only return a value if you find an exact match to A1
posted by inigo2 at 1:31 PM on July 10, 2012
A1 = value to be searched for
Worksheet1!A:C = search range; vlookup searches in the first column only, so this means search in column A on worksheet 1.
3 = the column in the search range to take the return value from; so column A = 1, B=2, and C (the one you want the return value from) = 3
0 = only return a value if you find an exact match to A1
posted by inigo2 at 1:31 PM on July 10, 2012
nevermind i'm wrong, shouldn't have been lazy and also missed your 2nd comment.
sorry. don't forget to anchor appropriately if you're copying this into other cells.
posted by saraindc at 1:31 PM on July 10, 2012
sorry. don't forget to anchor appropriately if you're copying this into other cells.
posted by saraindc at 1:31 PM on July 10, 2012
carmicha: "vlookup will only work if the list is in alphabetical order. You need index/match"
There's no need to have the source table_array sorted if you're looking for exact matches, as specified by the fourth argument.
posted by Perplexity at 1:33 PM on July 10, 2012 [3 favorites]
There's no need to have the source table_array sorted if you're looking for exact matches, as specified by the fourth argument.
posted by Perplexity at 1:33 PM on July 10, 2012 [3 favorites]
« Older Gotta scratch that Civ II itch. Any hope on Mac OS... | Removall or other eco-friendly paint removers for... Newer »
This thread is closed to new comments.
posted by stupidsexyFlanders at 1:23 PM on July 10, 2012