Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default hlookup function dilemma


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
--
RodJ
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default hlookup function dilemma

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

On Sep 29, 12:58*pm, RodJ wrote:
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
--
RodJ


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default hlookup function dilemma


Try this:

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

Regards,
Stefi

€˛RodJ€¯ ezt Ć*rta:


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
--
RodJ

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default hlookup function dilemma

Alternatively, to avoid using two HLOOKUP functions -

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

Muppet Man.

On Sep 29, 1:10*pm, Pete_UK wrote:
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

On Sep 29, 12:58*pm, RodJ wrote:



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
--
RodJ- Hide quoted text -


- Show quoted text -


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default hlookup function dilemma

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

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

I like this one better and more elegant

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


"RodJ" wrote:


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
--
RodJ



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default hlookup function dilemma

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


"Ashish Mathur" wrote:

Hi,

You could also use conditional formatting to conceal error values

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RodJ" wrote in message
...

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
--
RodJ


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
hlookup function ahiru Excel Worksheet Functions 5 March 30th 06 08:23 PM
HLOOKUP FUNCTION Effie Excel Worksheet Functions 0 May 24th 05 04:04 PM
HLookup? or an array function?? Murph Excel Worksheet Functions 5 May 11th 05 05:44 PM
HLookUp Function Louise Excel Worksheet Functions 4 February 25th 05 05:41 PM
Counting Function Dilemma Simon Lloyd Excel Worksheet Functions 0 November 8th 04 04:13 PM


All times are GMT +1. The time now is 09:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"