Vlookup

[Shona]

Hi

I have a vlookup formula which looks up a calculation but instead of getting
the figure I require I get #N/A eg

=VLOOKUP(F4,Sheet1!$A$2:$B$2492,2,0)

in cell F4 the calculation is

=(C4/B4)*100

is there another way I should be doing this?

Thanks for any help

Shona

 

[Andy]

Shona

I've just tried what you have and it works fine for me. Make sure the
VLOOKUP is checking like with like. It won't find numbers if it's looking
for text and vice versa.

Andy.

 

[Andy]

Shona

Have you checked that the number you are looking for is in the array?

Andy.

 

[Shona]

Hi

Yes it is thanks I've realised that if I change the last number from 0 to 1
it works

=VLOOKUP(F4,Sheet1!$A$2:$B$2492,2,1)

is this because the formula in F4 would return more than 2 decimal places
therefore that number can't be found in the array?

What I can't understand is that if all the cells are formatted to the same
why it doesn't work. Do I need to perhaps add a round up formula bit to the
formula in F4. If so how do I do that?


which was

=(C4/B4)*100

 

[Andy]

Shona

Formatting a cell to display 2 decimal places doesn't alter the number
itself.
You can either round F4, or round the vlookup function.
Use
=ROUND((C4/B4)*100,2) in F4
or use
=VLOOKUP(ROUND(F4,2),Sheet1!$A$2:$B$2492,2,1)

Andy

 

[Shona]

Thanks thats great

 

[Ecco]

One basic question. Have you sorted the range Sheet1!
$A$2:$B$2494 in ascending order? If not, please do so.

Ecco

 

[Myrna Larson]

If the formula in F4 returns a number with more than 2 decimal places, and all numbers in the
table have exactly 2 decimal places, you will get a #N/A result because you have used 0 as the
last argument in the VLOOKUP formula. If this is the cause of the problem, you have several
options:

1. Remove the ",0" at the end of the VLOOKUP formula
2. Change formula in F4 to =ROUND(C4/B4*100,2)
3. Change VLOOKUP formula to =VLOOKUP(ROUND(F4,2),Sheet1!$A$2:$B$2492,2,0)

 

[Myrna Larson]

VLOOKUP searches for the number that's actually in the cell. How that cell is formatted makes no
difference.