?
Solved

Tricky vlookup with if formula

Posted on 2014-07-20
1
Medium Priority
?
286 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

743 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