Solved

Tricky vlookup with if formula

Posted on 2014-07-20
1
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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,…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

724 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