hlookup function dilemma

[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

 

[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

 

[Stefi]

Try this:

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

Regards,
Stefi

„RodJ†ezt írta:

 

[MuppetMan]

Alternatively, to avoid using two HLOOKUP functions -

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

Muppet Man.

 

[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,"")

 

[RodJ]

Hi Ashish, I tried this and failed. I might have chosen the wrong option...