Vlookup formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tarheelfan - 19 Mar 2008 14:03 GMT

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.
A3:D18 covers all values in the table; I need the function to look at D18
but it is using D12 for the calculation. Any thoughts?

Reply to this Message

pdberger - 19 Mar 2008 14:14 GMT

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

> 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
[quoted text clipped - 6 lines]
> A3:D18 covers all values in the table; I need the function to look at D18
> but it is using D12 for the calculation. Any thoughts?

Reply to this Message

Max - 19 Mar 2008 14:15 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

tarheelfan - 19 Mar 2008 14:42 GMT

Thanks. That did it.

> > .. VLOOKUP(Z25,'Op Speed'!$A3:$D18,4)
>
[quoted text clipped - 15 lines]
> > A3:D18 covers all values in the table; I need the function to look at D18
> > but it is using D12 for the calculation. Any thoughts?

Reply to this Message

Max - 20 Mar 2008 00:05 GMT

welcome, glad it did.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks. That did it.

Reply to this Message