VLookup=#N/A?

**island1** · 01-24-2009, 03:55 PM

Why is this not working? I added the roundup column because i thought that might be the problem.

=VLOOKUP(0.18,B4:C2819,2,FALSE) result=#N/A

A B C D
GP% roundup profit price
0.35 0.36 $635.00 $1,800.00
0.35 0.36 $630.00 $1,795.00
0.35 0.35 $625.00 $1,790.00
continues
0.19 0.19 $265.00 $1,430.00
0.18 0.19 $260.00 $1,425.00
0.18 0.18 $255.00 $1,420.00
0.18 0.18 $250.00 $1,415.00
0.17 0.18 $245.00 $1,410.00
0.17 0.18 $240.00 $1,405.00

any ideas?

**shg** · 01-24-2009, 03:56 PM

Post the workbook?

**protonLeah** · 01-24-2009, 04:13 PM

Are the numbers in columns A & B calculated elsewhere?
Is the 0.18 in the VLOOKUP calculated elsewhere?

If the Col A/B nos. are calculated, reformat the cells to show several more decimal places, that might tell you why the lookupn is failing. As it is, the sample data you provided worked with the formula.

**island1** · 01-24-2009, 04:50 PM

sorry, here it is.

**VBA Noob** · 01-24-2009, 06:05 PM

This Array formula seemed to worked for me

Enter with Ctrl + Enter + Shift

=VLOOKUP(0.18,TEXT(B4:C103,"0.00")+0,2,FALSE)

VBA Noob

**island1** · 01-26-2009, 10:43 AM

Originally Posted by VBA Noob

Enter with Ctrl + Enter + Shift

It didn't work for me. What do you mean enter with Ctrl + Enter + Shift. Sorry I don't understand.

**Shijesh Kumar** · 01-26-2009, 10:49 AM

If the vlookup returns #N/A and you are sure that the lookup values exist, the

Select the lookup column..
Click on Data ...
Click on Text to column...

It sod hopefully wrk

**island1** · 01-26-2009, 10:49 AM

Nevermind. I did it. But I'm curious, what did that do? { }

**martindwilson** · 01-26-2009, 11:09 AM

it coerced the whole range b4:c103 as displayed in cell into text format "0.00" then added 0 to it to make it a number again but this time only 2 decimal places
TEXT(B4:C103,"0.00")+0
so your look up would have a chance of matching 0.18 in colummn B even if the underlying value was 0.180000001 or similar