I've read and tried every conceivable posibbilty I could think of, with no
succes.

The formula below results in #N/A if Q19 = nothing....how can I get a blank
cell result?

=IF(Q19=1,HLOOKUP(I34,G92:O97,3),IF(Q19=2,HLOOKUP( I34,G92:O97,4),IF(Q19=3,HLOOKUP(I34,G92:O97,5),IF( Q19=4,HLOOKUP(I34,G92:O97,6),""))))

Steve

Hi Steve

Maybe
=IF(Q19="","",HLOOKUP(I34,G92:O97,Q19+2)

Regards

Roger Govier


Steve K wrote:
I've read and tried every conceivable posibbilty I could think of, with no
succes.

The formula below results in #N/A if Q19 = nothing....how can I get a blank
cell result?

=IF(Q19=1,HLOOKUP(I34,G92:O97,3),IF(Q19=2,HLOOKUP( I34,G92:O97,4),IF(Q19=3,HLOOKUP(I34,G92:O97,5),IF( Q19=4,HLOOKUP(I34,G92:O97,6),""))))

Steve

Hi!

If Q19 = nothing then your formula returns "" (blank) in my tests.

If Q19 = something AND I34 = nothing THEN the formula returns #N/A.

Maybe try this:

=IF(OR(Q19="",I34=""),"",CHOOSE(Q19,HLOOKUP(I34,G9 2:O97,3),HLOOKUP(I34,G92:O97,4),HLOOKUP(I34,G92:O9 7,5),HLOOKUP(I34,G92:O97,6)))

Biff

"Steve K" wrote in message
...
I've read and tried every conceivable posibbilty I could think of, with no
succes.

The formula below results in #N/A if Q19 = nothing....how can I get a
blank
cell result?

=IF(Q19=1,HLOOKUP(I34,G92:O97,3),IF(Q19=2,HLOOKUP( I34,G92:O97,4),IF(Q19=3,HLOOKUP(I34,G92:O97,5),IF( Q19=4,HLOOKUP(I34,G92:O97,6),""))))

Steve

I like Roger's use of Q19+2

I'd use that formula but include the OR.

Biff

"Biff" wrote in message
...
Hi!

If Q19 = nothing then your formula returns "" (blank) in my tests.

If Q19 = something AND I34 = nothing THEN the formula returns #N/A.

Maybe try this:

=IF(OR(Q19="",I34=""),"",CHOOSE(Q19,HLOOKUP(I34,G9 2:O97,3),HLOOKUP(I34,G92:O97,4),HLOOKUP(I34,G92:O9 7,5),HLOOKUP(I34,G92:O97,6)))

Biff

"Steve K" wrote in message
...
I've read and tried every conceivable posibbilty I could think of, with
no
succes.

The formula below results in #N/A if Q19 = nothing....how can I get a
blank
cell result?

=IF(Q19=1,HLOOKUP(I34,G92:O97,3),IF(Q19=2,HLOOKUP( I34,G92:O97,4),IF(Q19=3,HLOOKUP(I34,G92:O97,5),IF( Q19=4,HLOOKUP(I34,G92:O97,6),""))))

Steve

Hi Biff

I agree, trapping the case of a null in I34 with the OR() function would
certainly make the formula more robust.

=IF(OR(Q19="",I34=""),"",HLOOKUP(I34,G92:O97,Q19+2 )

Regards

Roger Govier


Biff wrote:
I like Roger's use of Q19+2

I'd use that formula but include the OR.

Biff

"Biff" wrote in message
...

Hi!

If Q19 = nothing then your formula returns "" (blank) in my tests.

If Q19 = something AND I34 = nothing THEN the formula returns #N/A.

Maybe try this:

=IF(OR(Q19="",I34=""),"",CHOOSE(Q19,HLOOKUP(I34, G92:O97,3),HLOOKUP(I34,G92:O97,4),HLOOKUP(I34,G92: O97,5),HLOOKUP(I34,G92:O97,6)))

Biff

"Steve K" wrote in message
...

I've read and tried every conceivable posibbilty I could think of, with
no
succes.

The formula below results in #N/A if Q19 = nothing....how can I get a
blank
cell result?

=IF(Q19=1,HLOOKUP(I34,G92:O97,3),IF(Q19=2,HLOOK UP(I34,G92:O97,4),IF(Q19=3,HLOOKUP(I34,G92:O97,5), IF(Q19=4,HLOOKUP(I34,G92:O97,6),""))))

Steve