Vlookup sample problem

jenny - Jan 21, 2011 at 05:41 AM
 jenny - Jan 30, 2011 at 09:35 PM

I'm trying to learn vlookup and I made this sample problem and I'm not sure how I could get the formula done correctly to get the value I need.


name color fruit
jane red
dan green
lisa yellow

source data:
fruit name color
banana lisa yellow
apple jane red
mango dan green

to get the the return value of fruit above, I made this formula and it didn't work. Could anyone help me point out the error? Thanks.

=VLOOKUP(B2, $C$1: $E$4, 1, FALSE)

3 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 21, 2011 at 08:14 AM
The vlookup is like this (using you example)

=VLOOKUP(B2, $C$1: $E$4, 1, FALSE)

look at value B2
Match it at column C (the first column in $C$1: $E$4) decide which column to match against. If a match is found, then it will return the first column in range (C1 to E1, which basically means column C)

False means make an exact match

If no match is found, the functin will return errror like #N/A

It is hard to tell how your data is laid out as formatting is lost.
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.

your data need not be to be real data but a good representative of how data looks like
hi rizvisa1,
thanks for the response. I was able to get some of my examples correctly, it's just that on the example I gave above, I'm not sure if that's how vlookup search works. Please refer to my example below.

A B C ...... G H I
fruit name color name color fruit
banana lisa yellow jane red
apple jane red dan green
mango dan green lisa yellow

Now what I want to do is to search for the value in column I.
The formula I used was =VLOOKUP(H2, A1:C4, 1, FALSE), this gave an "#N/A" return.

Did I get that return because H2 match is in C column and not in A? Does the return I need has to always be on the next column and not before the column of the match I need? I noticed that I get the correct value only if the return I need is at the column preceded by the match column. How do I get the return I need if my sample data is presented that way? I'm not sure if this is a dumb question but I hope you could help me out with this. I would gladly appreciate your response. Thanks.
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 27, 2011 at 08:44 AM
Yes. Again in the VLOOKUP

the secord part "A1:C4" says that match will happen in column A, In your example the match of H2 is not in column A its in column C. SInce no match is found in column A, it returns u N/A.

Now if there is a case where the match column is not the first column, you may have to resort to use of MATCH, and INDIRECT function. Or you may have to use some temp column to make the left column as the first one in the lookuparray. So answer would depend on what one can or cannot do
Thanks again for the help, rizvisa1. You've explained it to me very well. =)