+ Reply to Thread
Results 1 to 4 of 4

ISBLANK with VLOOKUP where lookup value has IFNA function

  1. #1
    Registered User
    Join Date
    08-01-2019
    Location
    USA
    MS-Off Ver
    365
    Posts
    1

    Angry ISBLANK with VLOOKUP where lookup value has IFNA function

    I can't get the following formula to pull back any data:
    =IFERROR(IF(ISBLANK(VLOOKUP($B$8,DAL_Data!$F:$AT,41,FALSE)),VLOOKUP($B$8,DAL_Data!$F:$AT,3,0),VLOOKUP($B$8,DAL_Data!$F:$AT,41,0)),"")

    The DAL_Data column 41 has it's own VLookup with IFNA that returns a 0 (but I have tried to return " "). I need the result to be if column 41 has blank or 0, pull from column 3 instead. This is a critical issue that is needing ASAP. I have been playing with the formula to get it to pull data and it's not working.

    I have also tried to use the following formula:
    =IF(OFFSET(Toll_DAL_Data!N2,L3,0)=0,"",OFFSET(Toll_DAL_Data!N2,L3,0)) but that doesn't work either.

    I can send a par'd down excel if needed but can't attached since it has confidential information.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,347

    Re: ISBLANK with VLOOKUP where lookup value has IFNA function

    Unless something has changed in the newest versions of Excel, ISBLANK(VLOOKUP(...)) [or any function, for that matter] will return False. Spreadsheet functions must return something. Nothing in the spreadsheet repertoire (that I am aware of) will be recognized by the ISBLANK() function as blank. Empty string "", or space " ", or 0 are all non-blank numbers/strings. So, the condition part of your IF() function will always return FALSE, and it will always execute the value_if_false argument (which appears to repeat the same lookup).

    What characters/values/conditions are you needing to test for? I could see something like =IF(OR(VLOOKUP(...)=0,VLOOKUP(...)=" ",VLOOKUP(...)=""),VLOOKUP(...,3,0),VLOOKUP(...,41,0)) working, where you test the result of the lookup function against the three possible "error" conditions and, if any one of them is TRUE, pull from column 3*. The main problem I see is that, where you are using full column references, you expect that the lookup range could be very large, and exact match linear searches (4th argument of VLOOKUP() is 0/FALSE) are very slow. And, where we may need to perform the exact same lookup three times to test the three conditions, this has the potential to become very slow. A lot depends on how big your real data set will be and how many copies of this function you will have, just know that it has the potential to be a resource hog.

    Is it necessary to perform this test in this cell/function? It sounds like this test ultimately goes back to the IFNA() test in column AT of the lookup table. Could you take care of this in the lookup table? I expect that the current formula in the lookup table is something like =IFNA(lookup function,0). Could you change it to IFNA(lookup function,H2) [H is the third column of the lookup table]. By pulling the column 3 value into column 41 of the table upon error detection, the formula in the OP's cell no longer needs to test for the "error" condition. It can just pull the value from the 41st column =IFERROR(VLOOKUP($B$8,DAL_Data!$F:$AT,41,FALSE),"") without the need for the IF() test(s). Would something like that be allowed?

    * -- I could also see formulating this formula as =VLOOKUP$B$8,DAL_Data!$F:$AT,IF(OR(VLOOKUP(...)=0,VLOOKUP(...)=" ",VLOOKUP(...)=""),3,41),0) where you move the decision part to the column_number argument.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,744

    Re: ISBLANK with VLOOKUP where lookup value has IFNA function

    As MrShorty pointed out, a cell containing anything - be it data or a formula - is not blank. The ONLY time a cell will be considered blank is if it has absolutely nothing in it.

    Thats 1 of the main reasons I seldom use ISBLANK, instead I prefer to test for ="" This will work for both (truly) empty cells and formulas that return a nul string ""

    can you provide some context (data) on how you are using this?
    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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: ISBLANK with VLOOKUP where lookup value has IFNA function

    Is this what you need?

    =IF(VLOOKUP($B$8,DAL_Data!$F:$AT,41,FALSE))="",VLOOKUP($B$8,DAL_Data!$F:$AT,3,0),VLOOKUP($B$8,DAL_Data!$F:$AT,41,0))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. Combination of a lookup and an IFNA?
    By Weaselwithagun in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-15-2019, 11:54 AM
  2. [SOLVED] What function can be combined with IFNA in Excel Office365 / 2016
    By jpl69 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-25-2018, 09:18 AM
  3. [SOLVED] IFNA function and text into value
    By mcgleeuw in forum Excel Formulas & Functions
    Replies: 34
    Last Post: 02-09-2017, 10:21 AM
  4. Help with adding ISBLANK to a VLOOKUP function
    By llubelczyk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2014, 02:10 PM
  5. Complicated VLookup Function with =IF(AND(NOT(ISBLANK
    By chrishull1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2014, 01:31 PM
  6. PLEASE HELP: ISERROR, ISBLANK with LOOKUP
    By NoJokenVA in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2013, 04:45 PM
  7. Help with ISBLANK function referring to a cell with another ISBLANK formula
    By camdameron in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-23-2011, 12:45 PM

Tags for this Thread

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