Simple formula NOT working :(

[Danny Lewis]

Hi all,

I have a formula I am trying to put together and it just isn't working,
despite it's simplicity - and wondered if anyone could point out where I'm
going wrong...

In cell C20 are the letters MP.
In cell AG20 (the 21st column in the vlookup below), is the figure 3.
In cell AT20, is the figure 37.

In cell Summary!$Z$7 is the number 17.

In cell D10 is the following formula:
=IF(OR(AT20=0,VLOOKUP($C20,$C$4:$AP$108,Summary!$Z$7+4,FALSE)=0),"",IF(VLOOKUP($C20,$C$4:$AP$108,Summary!$Z$7+4,FALSE)>AT20,"ALARM",""))

The lookup identifies the MP, finds the 21st column in the lookup, which
seems to be the figure 3, and compares it to AT20, which is 37. If AT20 is
smaller than the 21st column figure (3 in this case), "ALARM" is displayed.
However 37 is clearly not smaller than 3. And the formatting is correct.

Anyone see why the formula results in "ALARM"???

Thanks in anticipation

Danny

[SIR Knight]

Danny,

In your range for the Vlookup, column W is the 21st Column. Column AG
is the 31st Column

If you have hidden columns, they will still be counted by the vlookup

Steve

[Danny Lewis]

Yes, thank you - my predecessor had hidden those columns without me noticing
- silly me huh

Thanks for pointing that out...

"SIR Knight" wrote:

>
> Danny,
>
> In your range for the Vlookup, column W is the 21st Column. Column AG
> is the 31st Column
>
> If you have hidden columns, they will still be counted by the vlookup
>
> Steve
>
>

[Bob Phillips]

It works fine for me. I would point out that column 21 for me in that
formula is W, not AG.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny Lewis" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I have a formula I am trying to put together and it just isn't working,
> despite it's simplicity - and wondered if anyone could point out where I'm
> going wrong...
>
> In cell C20 are the letters MP.
> In cell AG20 (the 21st column in the vlookup below), is the figure 3.
> In cell AT20, is the figure 37.
>
> In cell Summary!$Z$7 is the number 17.
>
> In cell D10 is the following formula:
>
=IF(OR(AT20=0,VLOOKUP($C20,$C$4:$AP$108,Summary!$Z$7+4,FALSE)=0),"",IF(VLOOK
UP($C20,$C$4:$AP$108,Summary!$Z$7+4,FALSE)>AT20,"ALARM",""))
>
> The lookup identifies the MP, finds the 21st column in the lookup, which
> seems to be the figure 3, and compares it to AT20, which is 37. If AT20 is
> smaller than the 21st column figure (3 in this case), "ALARM" is
displayed.
> However 37 is clearly not smaller than 3. And the formatting is correct.
>
> Anyone see why the formula results in "ALARM"???
>
> Thanks in anticipation
>
> Danny