Solved

Tricky vlookup with if formula

Posted on 2014-07-20
1
264 Views
Last Modified: 2014-07-20
Hi experts excel 2010

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, so the formula copies the data entry from cell A2 and in cell A3 the lookup applies. Formula in cell b2. Etc...

So
Col A.          Col B.
Wcd.            Wcd.
                     Here blank look finds value from look up table
Adds.           Adds.

Etc...
0
Comment
Question by:route217
1 Comment
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40208041
Assuming the formulas will be in column B, I think it would look something like this (in cell B2, copied down):
=IF(A2<>"",A2,IFERROR(VLOOKUP(A1, table_array, col_index_num, [range_lookup]),"")

This isn't really applicable on row 2, but when copied down the formula will check if there is a blank value in the adjacent cell.  If the cell isn't blank, then the formula returns the same value as the adjacent cell.  Otherwise (if blank) it does a VLOOKUP on the immediately preceeding value (one row above).

One problem with this is that it won't work if there are blank values in consecutive rows, like so:

A3: wcd.
A4:
A5:
A6: wcd.

B4 will do a lookup using "wcd.", but B5 will have nothing to lookup since A4 is blank.

-Glenn
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Unable to open excel in 2016 is slow 4 21
Excel case statements 3 25
Excel 2016 formulas 5 32
Excel VBA - merge "specific" data from workbook in to "new" workbook. 19 24
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,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 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

24 Experts available now in Live!

Get 1:1 Help Now