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

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

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

Thanks yes I found ' infront of some of the email adress's so I used CLEAN and TRIM

Good site Thanks

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.

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

=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

Ask Your Question

Weekly Poll

When do you think 3D printing will become mainstream?

Discuss in The Lounge

Poll History