hlookup function dilemma

R

RodJ

the result of my hlookup is #N/A as my formula reads:
hlookup(A1,A2:A6,1,false).

Question: How do I make this #N/A invisible? I would rather see a blank cell
as it clutters my spreadsheet.

Thanks
 
P

Pete_UK

Do it like this:

=IF(ISNA(hlookup(A1,A2:A6,1,false)),"",hlookup(A1,A2:A6,1,false))

ISNA checks for #N/A and returns a blank if necessary.

Hope this helps.

Pete
 
S

Stefi

Try this:

=IF(ISERROR(HLOOKUP(A1,A2:A6,1,FALSE)),"",HLOOKUP(A1,A2:A6,1,FALSE))

Regards,
Stefi

„RodJ†ezt írta:
 
M

MuppetMan

Alternatively, to avoid using two HLOOKUP functions -

=IF(COUNTIF(A2:A6,A1)>0,HLOOKUP(A1,A2:A6,1,FALSE),"")

Muppet Man.
 
T

Teethless mama

To all: Pete UK, MuppetMan, and Stefi. Why HLOOKUP? should be VLOOKUP instead.

=IF(ISNA(VLOOKUP(A1,A2:A6,1,0)),"",VLOOKUP(A1,A2:A6,1,0))

I like this one better and more elegant

=IF(COUNTIF(A2:A6,A1),A1,"")
 

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

Top