Vlookup formula

[tarheelfan]

I am running into a problem where my Vlookup function is not finding certain
data from the table it is referenced to. I'm using the formula
=IF(OR(Z23="",Z25="",Z26=""),"",Z26/(Z23*(VLOOKUP(Z25,'Op
Speed'!$A3:$D18,4)*60))).
This formula is working for most of the values in the table, but seems to
miss only a certain few. Case in point, the function should be locating the
number 120 to calculate the percentage I want, but it is finding the number
70.
A318 covers all values in the table; I need the function to look at D18
but it is using D12 for the calculation. Any thoughts?

 

[pdberger]

THF --

I suspect the problem is that the index values in the lookup range are not
in ascending order. If that's the case, then your VLOOKUP formula is missing
an argument -- I think it'll need to be VLOOKUP(Z25,'Op
Speed'!$A3:$D18,4,FALSE).

HTH

 

[Max]

.. VLOOKUP(Z25,'Op Speed'!$A3:$D18,4)

1. Fix the reference range: 'Op Speed'!$A$3:$D$18
2. Are your lookup ref values in A3:A18 sorted in ascending order? Maybe you
should be searching for an exact match, using FALSE (or zero) as the 4th
param?

Suggest you try it as: VLOOKUP(Z25,'Op Speed'!$A$3:$D$18,4,0)
in your expression

 

[tarheelfan]

Thanks. That did it.

1. Fix the reference range: 'Op Speed'!$A$3:$D$18
2. Are your lookup ref values in A3:A18 sorted in ascending order? Maybe you
should be searching for an exact match, using FALSE (or zero) as the 4th
param?

Suggest you try it as: VLOOKUP(Z25,'Op Speed'!$A$3:$D$18,4,0)
in your expression

 

[Max]

welcome, glad it did.