VLOOKUP wont work not working correctly

December 21, 2012 at 07:10:04
Specs: Windows 7
Please HELP

Ive got a Vlookup buts its not seeing all the data correctly and I cant work out why.

Although I can there should be a match is comes up error Hash N/A


See More: VLOOKUP wont work not working correctly

Report •


#1
December 21, 2012 at 08:19:56

Report •

#2
December 21, 2012 at 08:33:43
Without seeing your data and formula, we can't offer too much more than phil22 has offered.

As noted at that website, #N/A can often be the result of data that you think matches but in reality doesn't.

One quick way to check is to visually find what you think is a match and use a simple formula to see if they really do match.

e.g. With your lookup value in A1 and your "visual match" in C11, enter this formula in any cell:

=A1=C11

If the formula returns TRUE, then it is an exact match and you should look at your VLOOKUP formula. If the formula returns FALSE, then it is not an exact match.

There could be a space, a hidden character, etc.

As I said, without more information we can't offer anything more specific.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
December 22, 2012 at 10:41:55
Thanks yes I found ' infront of some of the email adress's so I used CLEAN and TRIM

Report •

Related Solutions

#4
December 22, 2012 at 10:43:51
Good site Thanks

Report •

#5
December 22, 2012 at 15:54:25
The single quote forces Excel to format the value in the cell as Text.

I often use a single quote in front of an email address to prevent Excel from creating an email address hyperlink when I don't need one.

That said, a single quote - by itself - should not cause VLOOKUP to fail. I just tested it with an email address that Excel turned into a hyperlink and the same email address forced to be text with a single quote.

I tried VLOOKUP in "both directions" and it found the both the hyperlink email address and the text email address.

If your VLOOKUPs were failing, I'm thinking that something else was in the cell besides the single quote.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 23, 2012 at 07:30:10
Further to Vlookup explanations I dont seem to be getting it

=VLOOKUP(A5,New_keep_fmt!A5:B32,10) I have emory_Cossins@hotmail.com in A5 and this also appears within the range A5:B32 I thought it looked along the line until column 10 and returned the value

How do I get it to look along the line when it finds emory_cossins@hotmail.com and return his 1st Name and then Suname in Column Y&AA

Many many thanks


Report •

#7
December 23, 2012 at 07:42:44
=VLOOKUP(A5,New_keep_fmt!A5:B32,10)

Vlookup can't look in a cell you have NOT defined as part of your table array range.

If your looking for data that is 10 columns over,
then your table array range (A5:B32) must also go 10 columns over
so something like

=VLOOKUP(A5,New_keep_fmt!A5:J32,10)

Look here for a more complete explanation:

http://www.techonthenet.com/excel/f...

MIKE

http://www.skeptic.com/


Report •


Ask Question