VLOOKUP - Hide #N/A ERROR

drdumont

New Member
Joined
Aug 8, 2007
Messages
12
I'm using vlookup to search a block of data with a max of 31 rows, and a min of 0 rows.

This is data from a calendar in spreadsheet form. Only dates with work (event column) occur. So if there is work every day, then there would be 31 lines, or 30 lines or less in appropriate months with <31 days.
All my calculations regarding vlookup work (when there is data on that line), I wont bore you with them here.



PROBLEM:
On the calendar which uses vlookup to get the data, it keys on the first column. On those dates (like 11/7) which don't exist, or in a 30 or 28 day month, I get #N/A errors, which is logical. I just want to hide the #N/A display.
--------------------------------------------------------------
11/3 <Enter Data Here to Look Up Info in column g on that day (ROW 1)

bkb (result for vlookup(a1,b7:f37,4,false) if entry in b7 exists in cells b7:b37 (Row 3)

#N/A (result if data not in b7:b37. I just want to hide this) (ROW 4)


A B C D E F
1 (ROW 7)
2 11/1 11/1 11/1 soc cll (ROW 8)
3 11/3 11/4 11/6 bkb aus
4 11/9 11/11 11/11 vb dal
5
6
<SNIPPED FOR BREVITY>
28 NO DATA ON DAYS WHICH DON'T EXIST
29 NO DATA IF NO EVENT SCHEDULED THAT DATE
30 (ROW 36)
31 (ROW 37 )


I'VE TRIED:

=iferror(VLOOKUP(a1,b7:f37,4,FALSE),"") -- gives me the response #NAME?
(is IFERROR really a function or is that an error in the "help" database???)

=iserror(VLOOKUP(a1,b7:f37,4,FALSE)) -- returns "TRUE", I want a blank cell!!

I could go on adding more =if statements, but that gets too crazy.

I can't post a picture, and this message board's formatting screws things up, but you can see the sheet at

drdumont.webs.com

In a nutshell, I'd like to display a blank cell if VLOOKUP encounters ANY error.

Any help would be greatly appreciated, thanks in advance

-- Doc
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
IFERROR is a function only available in XL2007 or higher.

If that returned #NAME? error, then I assume you have 2003 or earlier.

In 2003, try

=IF(ISNA(VLOOKUP(a1,b7:f37,4,FALSE)),"",VLOOKUP(a1,b7:f37,4,FALSE))
 
Upvote 0
You guys ROCK! Thanks for the info. The IFERROR threw me, the MS help tossed that grenade under my chair with no disclaimer re 2007.
And after looking at the examples you game me, I now see where I went down the wrong track.
Your help woiks like a chom, as my old grandpa used to say. Thanks again!

-- Doc
 
Upvote 0
JONMO1: Hey to Bryan/College Station. Was born in C.S., went to A&M, Boy, has THAT place changed! I was Chief Engineer of WTAW a few years, built KAMU-TV. I owe you a beer at Martin's!
-- Doc
 
Upvote 0
Glad to help, thanks for the feedback.

Yep, just like everywhere else. Always growing..
 
Upvote 0

Forum statistics

Threads
1,223,204
Messages
6,170,722
Members
452,351
Latest member
Giare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top