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!
posted by stupidsexyFlanders to Computers & Internet (11 answers total) 1 user marked this as a favorite
 
Response by poster: If it makes it easier, in my Worksheet 1 table, whereever the word apple appears in the first column, the corresponding contents of the cell two columns to the right will ALWAYS contain the word "crunchy". We don't have to account for the possibility it might say "shiny" instead.
posted by stupidsexyFlanders at 1:23 PM on July 10, 2012


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


=VLOOKUP(A1,Worksheet1!A:C,3,0)
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


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]


=VLOOKUP(A1,Sheet1!$A:$C,3,FALSE)
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


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


Yeah; I'd actually use $A1 and Worksheet1$A:$C
posted by inigo2 at 1:32 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]


D'oh!
posted by carmicha at 1:47 PM on July 10, 2012


« 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.