[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Vlookup  up to find only empty cell in a column

Posted on 2014-07-18
Medium Priority
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
  • 2
  • 2

Author Comment

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

Expert Comment

by:Glenn Ray
ID: 40205718
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

ID: 40205917
Thanks Glenn excellent feedback.

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

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40205924
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.


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 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