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

#N/A# when doing a vlookup

H

Hayley

Member
ARGH! VLOOKUP just *never* goes right for me!


I have 2 sets of data on one sheet. The first set is A2:C59833.


The second set is G2:J4261.


In column H of the second data set I have ID#s.

In column A of the first data set I also have ID#s. Both data sets are sorted by ID#s in ascending order.

In column C of the first data set I have member numbers (the information I'm interested in finding).


So in column K I wrote this formula: =VLOOKUP(H2,$A$2:$C$59833,3,FALSE)

...saying I want to look in the first data set for the number that's in H2 (from the 2nd data set) and return back the number in column C (i.e. the 3rd column).


I get #N/A# for every result and I can *see* that the numbers are there in the first data set that I'm doing the lookup on!!


What am I doing wrong?


Thanks...
 
I'd recommend doing a check to make sure they really are the same. Perhaps a simple formula of:

=H2=A6 (or whatever is the correct cell)


If True, there's something wrong with current formula. If false, there may be some trailing spaces or something that are causing problems. This most often occurs with imported/copied data.


You mentioned that the data is sorted, but you're finding an exact match. Technically, data doesn't need to be sorted if looking for exact match...or were you actually wanting to find the closest match?
 
Hi Hayley,,


Same here.. but with a little different.. VLOOKUP *never* goes LEFT for me.. ha ha ha..


Can you please try both the below formula.. and confirm if any of them works for you.. If yes, then confirm which one, and we will discuss about the solution.. :)


Code: 
=VLOOKUP(VALUE(TRIM(H2)),$A$2:$C$59833,3,0) or =VLOOKUP(TEXT(H2,"@"),$A$2:$C$59833,3,0)


Regards,

Deb
 
@Luke M you're correct!!! I got FALSE! I don't know why, but then @Debraj Roy I used the first formula and it worked! (2nd formula did not)


What the heck? What just happened? What did that all mean?


PS: Thank you!!
 
Hi Hayle..


* Select Column H,

* Go To Data > Text to Column

* Click Next > Next > Finish..


Now In K2 use YOUR own Formula

So in column K I wrote this formula: =VLOOKUP(H2,$A$2:$C$59833,3,FALSE)
Click to expand...

It will work..


ID's in your first Data Set are NUMERIC.. but ID's in you Second Data Set are TEXT.. both are different but looks like same... When you convert's the second Data Set via TEXT TO COLUMN, it will convert all Text Data to Numeric Data..


Regards,

Deb
 
@Deb

I think you got your solutions flipped. Since first formula worked, it just means there's extra spaces in col H.


@Hayley,

You could either just use Deb's formula which contains the TRIM function, or you could create a helper column to TRIM the data for you. Up to you really.
 
@ Luke,


* 1st Formula, assuming H column as Text and A Column as Numeric (I have used VALUE and Trim)

* 2nd Formula, assuming H column as Numeric and A column as Text (I have used TEXT)

* JUST Extra Space is not possible in Numeric.


and whenever we convert Text to Column, it always change all TEXT cum Numeric's to Numeric..

I think, my solution was not flipped..


No Offense, until OP was helped..


Regards,

Deb
 
Hi Deb ,


Just BTW , but I think you will find this instructive :


=VALUE(...)


by itself , elminates leading and trailing spaces. So , the TRIM is not necessary.


Narayan
 
Hi Narayan..


If
Code: 
=CELL("Type",A1)
is "V", then it not possible to add Extra Space..

Correct me if I am wrong..


Regards,

Deb
 
Hi All, just wanted to mention I had the *same* problem again today and went back to this thread. I did the Data>Text to Columns for both columns just to be safe and it fixed it, allowing me to use vlookup.


Seriously, you guys are THE BEST!!!
 
You must log in or register to reply here.
Back
Top