Vlookup sample problem
Solved/Closed
Related:
- Vlookup sample problem
- Transfer data from one excel worksheet to another automatically vlookup - Guide
- Vlookup if cell contains partial text - Office Software Forum
- Vlookup date 1/0/1900 ✓ - Excel Forum
- Sbi bank statement sample pdf download - Guide
- How to subtract in excel using vlookup ✓ - Office Software Forum
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 21, 2011 at 08:14 AM
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.
Note: your data need not be to be real data but a good representative of how data looks like
=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.
Note: 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.
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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 27, 2011 at 08:44 AM
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
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
