vlookup - hit & miss!!

Originally Posted by rac1974

Hi all,

I'm having a problem with my vlookups. It's returning almost everything correctly, however it's returning #N/A in some cells when in fact it should be returning a figure. My formula is 100% correct - it just seems a bit temperemental and not recognising the data when it should be. I'm looking up a different worksheet. and my cells are all formatted to number.
It's driving me crazy!!!

Hi,

if you are using VLookup( , , ,True) and get #N/A then your lookup value is lower than the first value in the table, or the table is not sorted.

If you are using VLookup( , , ,False) then your lookup value does not exist in the table in a recognised form.

can you (without giving away secrets) post a portion of your table and the formula as you are using it, together with a value that misses?


added note, if your Lookup value is a calculation and you are using the False option, then you may be failing (what I think of as the 10c rule) the .1 number, try looking up the relavant Integer, or, for an amount, the (Integer(A1*100))/100 etc.

---

Originally Posted by rac1974

Thanks

File Help1 Sheet1 cell A2 refuses to work when it looks up 1010ILU. Change the ILU to something else and it works. It seems not to like ILU and other combinations of letters.

Hi,

you said before that your cells were all formatted to Numbers, are you trying to lookup 1010ILU as a number? and are you using the True or False vlookup?

---

Originally Posted by rac1974

heres the files

the item in A2 of Help 2 is

1010ILU

try =Len(A2) to see that it is 16 characters long

try ="kk"&A2&"kk" to confirm

that will probably solve your non-match.

to fix, use =Trim(A1) down a spare column and copy, Paste Special = Values back over the A column.

hth
---

Originally Posted by rac1974

you're a champ

Thanks for your help

np - good to see a resolution, and thanks for the feedback

---