Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Tricky vlookup with if formula

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
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 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.

564 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