+ Reply to Thread
Results 1 to 2 of 2

VLOOKUP formula returning a value for a blank cell that I wish to note as "Not in Master"

  1. #1
    Registered User
    Join Date
    07-24-2015
    Location
    Fairhope, AL, United States
    MS-Off Ver
    16.35 (Office 365)
    Posts
    40

    VLOOKUP formula returning a value for a blank cell that I wish to note as "Not in Master"

    Hello,
    I am using Microsoft Excel for Mac ver 15.3, and I am trying to combine the LOOKUP and IFERROR formulas to pull data from sheet 1 titled Student List to another sheet in the workbook named Student Information Form. It works fine except when the cell on the source sheet is blank. If a student name is blank, then I get a "0" value in the form sheet cell, and if the birthday is left blank, I get "January 1, 1900" in the form sheet cell. The Birthday cell is formatted as Date. I have used this formula below and get the desired results when data is located in the source sheet, but I cannot figure out how to insert the message "Not Listed in Master" when the source cell contains no data.

    =IFERROR(VLOOKUP(B2,'Student Sheet'!B4:S40,[column number],False),"Not Listed in Master")

    If anyone has a workable solution, I would be grateful for a reply.

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,050

    Re: VLOOKUP formula returning a value for a blank cell that I wish to note as "Not in Mast

    IFERROR wont work here because there really is no error, Br is indeed being found in the search range, there is just nothing to return from the column you want. the error would be if B2 was not found

    Perhaps try this...
    =if(VLOOKUP(B2,'Student Sheet'!B4:S40,[column number],False)=0,"Not Listed in Master",VLOOKUP(B2,'Student Sheet'!B4:S40,[column number],False))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. solution for the blank cell using the IF function
    By ragnaedge in forum Excel General
    Replies: 4
    Last Post: 08-23-2013, 11:03 AM
  2. Formula is returning "FALSE" instead of "" (blank)
    By wvpersephone13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2011, 04:31 PM
  3. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  4. Replies: 4
    Last Post: 02-05-2009, 09:50 AM
  5. Replies: 5
    Last Post: 09-19-2008, 04:02 PM
  6. Replies: 1
    Last Post: 04-12-2007, 09:33 PM
  7. Replies: 3
    Last Post: 12-14-2006, 01:36 PM
  8. Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
    By joisimha@gmail.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1