Another #N/A Result question...Sorry

G

Guest

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
 
R

Roger Govier

Hi Steve

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

Regards

Roger Govier
 
B

Biff

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
 
R

Roger Govier

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup giving #N/A 12
Problem with HLookup. Not sure if it is a bug or not 3
Using COUNTA for a variable array 5
I dont want #N/A! 11
LOOKUP function yielding a #N/A result 6
Help with N/A in formulas 3
#N/A result because data is on another worksheet 8
Working out a capped point score using LARGE function 2

Top