Vlookup  up to find only empty cell in a column

Posted on 2014-07-18
Last Modified: 2014-07-19
Hi experts excel 2010

How would you write a formula so if in column A cell 2 you have a data value and cell A3 is empty then in A3 then lookup applies but ignore all cells in column a where you already have a value..

Col A.          Col B.
Wcd.            Lookup return nothing
                     Here blank look finds value from look up table
Adds.           lookup returns nothing

Question by:route217

    Author Comment

    The formula has to apply to both text data and numerical data...
    LVL 27

    Expert Comment

    by:Glenn Ray
    The formula would be something along this line (inserted in B2 and copied down):
    =IF(A2<>"","",VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]))

    We may need to see a more-detailed example if this isn't helpful.


    Author Comment

    Thanks Glenn excellent feedback.

    If I amend formul to =if (isna (A2 <>"","", vlookup etc...would this work also..
    LVL 27

    Accepted Solution

    I assume you want error handling for the VLOOKUP.  In that case the formula would be revised as follows:
    =IF(A2<>"","",IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]),"")

    Since you have Excel 2010, the IFERROR function is a much better tool to handle #N/A values on VLOOKUPS.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now