Return Text Based On Lookup Value

  • I have a database of customers that are all sorted by a customer number.


    I have a form that has a number of fields that display customer info. When I use a blank of this form and put the customer number in it's correct field, all the other info fields do a vlookup on the customer number in the database file and return the info, such as phone numbers, name, address, etc.


    Here's my question;


    How can I check for a blank (using ISBLANK, I assume) to check to see if the customer number exists in our database and, if it's NOT blank, operate on it with the following example which is used to return their insurance expiration date.


    =IF((VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))


    I realize that this may be a sledgehammer approach and that Access is probably a better tool, but my company has not shelled out the $$ for Access...yet.

  • Re: Check For Blank Then Vlookup


    To see if the customer number exists in your database, I don't think ISBLANK comes into play. You would check to see if the result of your VLOOKUP returns an error; if so, the value was not found. BTW I think you had superfluous parens. VLOOKUP returns #N/A if a value is not found, and the ISNA function tells you whether a value is being returned as #N/A.


    Try this. I don't have your file setup so I haven't tested it.


    =IF(ISNA(VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE)),"CUSTOMER NOT FOUND",IF(VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE)<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE)))

  • Use a lookup table. Lookup table OR better still, use a single cell: Lookup Value & Return Corresponding Result


    OR;


    Why not address the issue at the LOOKUP level?


    The single best and most efficient way is to do this is;


    1) Add your lookup formula to a spare column (e.g Column "A") and allow the #N/A! to happen.


    2) Now reference these cells from the required cells like this;
    [frc]=IF(ISNA(A1),0,A1)[/frc]


    3) Hide Column "A" by selecting it and going to Format>Column>Hide


    The second most efficient is probably like this;


    Instead of;
    [frc]=VLOOKUP("Dog",A1:D100,2,False)[/frc]
    Use;
    [frc]=IF(COUNTIF(A1:A100,"Dog"),VLOOKUP("Dog",A1:D100,2,FALSE),0)[/frc]


    The least efficient method is like;
    [frc]=IF(ISNA(VLOOKUP("Dog",A1:D100,2,FALSE)),0,VLOOKUP("Dog",A1:D100,2,FALSE))[/frc]


    If you don't like the Zeros showing you can hide them via Tools>Options>View - Zero values. Or, cell-by-cell with a Custom Format like: General;-General;