• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup Returning an N/A When Searching on a Name

P

Patrice71

New Member
Hi everyone! I'm new to this forum and really need some help. I have no idea why my vlookup formula is not working and I'm so frustrated! I'll give you an example of what I'm trying to do: =vlookup(b2,Sheet2!A2:c4,3,0)


1 Harry ________ 1 Harry $35.20

2 Sally ________ 2 Sally $25.10

3 Fran ________ 3 Fran $36.21


I'm trying to return the dollar value for Harry from table 2 into table 1 by searching on Harry's name, but it's coming back N/A. When I use the same formula, but search on the "number 1" for Harry it returns the $35.20 perfectly so it's something with the text in Harry's name. I've copied the names from table 1 into table 2 so how could the formatting be the problem? It's not imported or anything like that. I've tried to make the text a number and that didn't work. I have no idea what's wrong.


Please help!


Thanks!

Patrice
 
Hello Patrice,


Welcome to Chandoo.org.


You need to change VLOOKUP to align where Harry is located so it should be:

=VLOOKUP(B2,Sheet2!B2:C4,2,0)


The lookup_value must be in the left most column to get correct results.
 
Hi Patrice71,


Welcome to the forums!


Following is your data"

[pre]
Code: 
Harry	1	 $35.20
Sally	2	 $25.10
Fran	3	 $36.21
[/pre]
I just tried out on my worksheet, your formula works perfectly. EXCEPT, you might have some extra spaces after Harry so check out your table for extra spaces, "Harry " is different from "Harry".


Regards,
 
Hi shrivallabha,


I made a mistake when I said A2 instead of b2 in my formula above. My formula still returns an N/A when the formula is done properly and I'm pretty sure it has something to do with the fact that "Harry" is text because when I search on "number 1" I get $35.20.


Thanks,

Patrice
 
Hi Faseeh,


How can the spacing be my problem when I'm actually copying the name "Harry" from table 1 into table 2? I've also done the "text to columns" to get rid of any additional spacing, but that didn't work. Ugh....
 
Practice71,


Can i have a sample file?? Upload it to any file sharing service and drop a link here.


Regards,
 
Hi Patrice,

The other part of shrivallabha suggestion was that the column indicator needs to change. In the condition that works, you're looking for a number and then returning the value in 3rd column; table is A:C, lookup value in col A, return value in col C. In formula that doesn't work, where name is used, need to use 2nd column, since B:C is table, lookup value is col B, return value is col C. The clarify, the column indicator is relative to table range, not absolute column reference.


Assuming the following is your table in cells A2:C4

[pre]
Code: 
1	Harry	$35.20
2	Sally	$25.10
3	Fran	$36.21
[/pre]
The fomrula to lookup Harry's value is:

=VLOOKUP("Harry",B:C,2,FALSE)
 
Hi Luke M,


I want to be able to search and return on all of the names in the name column, not just Harry and return their dollar values. I don't know if that formula you have above will do that?


Thanks,

Patrice
 
@Patrice

I was just posting an example. To make formula more versatile, just use a cell reference as applicable for first argument. Making a SWAG at where the cells are really at:

=VLOOKUP(A2,Sheet2!B:C,2,FALSE)
 
Luke M,


I am using 2 tables, not just one. When I try to copy and paste the info it gets all bunched up and messy. I don't know how to show you what I mean?
 
When I use this formula:


=VLOOKUP(B4,G:I,3,0) which is referring to the table that's on the same page, I get a value. When I use the formula above Use =VLOOKUP(B4,Sheet2!$B$5:$C$8,2,0), I don't get a value. The only thing I can think of is that in the =VLOOKUP(B4,G:I,3,0) formula I just took out the row numbers and for some reason the value comes in? That's the only thin I can think of that changed?
 
Oh, nevermind. That formula didn't work either. It should have said =vlookup(c4,G:I,3,0)so that it was searching on Harry. My formula above was searching by 3654.
 
Hi Pratrice71,


Now let me explain you all the formula that you have mentioned:


1. VLOOKUP(B4,G:I,3,0)


This will give you zero indifferent to whatever name you put in. The formula will look for the value in B4, and will match it will column G but will retrun result from column I that is blank so will always show zeros.


2. VLOOKUP(B4,Sheet2!$B$5:$C$8,2,0)


..Will lookup for value of B4 in Column B of Sheet2 and return value from the second column to the column B, i.e. column C.


Summary: VLOOKUP() goes for the first column of the table to check for values, counts it as one and will move forward to right counting second, third..., nth column as you mention in your formula and retrieves a value. It will give NA error if you ask for third column and put in table only two.


Regards,
 
When you paste data here form XL, use backticks (key right above Tab, left side of keyboard) to enclose the info.


Hope you can post your able and it will make things clearer. I've gotten confused as to what the actual location/layout is. =(
 
@ Luke,


You can download the file, i suggested a solution and it is working on my pc. I think OP is unable to understand VLOOKUP().


Faseeh
 
Sorry Faseeh, but I still suffer from CASFFML (can't download files at my location). =(
 
You must log in or register to reply here.
Back
Top