Copy and Replace with Vlookup Formula

[vbanoob1234]

Hello everyone,

My goal is to find all YELLOW highlighted cells that are currently empty, and replace it with a vlookup formula like this:
=Vlookup(A1,A:Z,2,False)

The issue is when you use the Find and Replace function, the lookup value of A1 is an absolute value. I need the lookup value to be relative.
Eg.
A1 =Vlookup(A1,A:Z,2,False)
A2 =Vlookup(A2,A:Z,2,False)
A3 =Vlookup(A3,A:Z,2,False)
A4 =Vlookup(A4,A:Z,2,False)

The reason why I am asking this, is because my goal is to put a vlookup on every other line in one specific column. However, there are subtotals in the way. That is why I can't copy and paste the formula, because it will override my subtotal (sum) formula.

Thank you in advance

 

[d3skjet]

What column are the (possibly) empty cells in? How many rows are in column A, and can this vary?

 

[Jonmo1]

You can highlight the entire column where the formulas will go
Press CTRL + G
Click Special
Select Blanks
Click OK

Type the formula as it should appear in the FIRST highlighted cell from previous step DO NOT PRESS ENTER
Press CTRL + ENTER

Hope that helps.

 

[vbanoob1234]

@d3skjet - thanks for your reply.

What column are the (possibly) empty cells in?
Columns - there are 4 or 5 columns depending on the file.
How many rows are in column A, and can this vary?
There are over 2000 rows in Column A, and this may vary.

 

[vbanoob1234]

Hi Jonmo1

Thank you for your reply. Unfortunately, this did not work. Am I doing something wrong here? I have Excel 2010.

 

[vbanoob1234]

On another Jonmo1, the formula appeared on the unhighlighted cells.
I want this to apply on the HIGHLIGHTED cells.

Maybe I am doing something wrong. hmmm

 

[Jonmo1]

Can you describe "did not work" ?
What exactly happened when you tried it?

Are your blanks not really blank, but formulas returning "" ?

 

[vbanoob1234]

It put formulas in both the highlighted cells, and non-highlighted cells.

I only wanted it for Highlighted cells.

Thank you in advance!

 

[Jonmo1]

Just to clarify...

Highlighted Cells are blank/empty
NON-Highlighted cells contain a Subtotal
You want to put the vlookup in the Highlighted/blank/empty cells

Right?

The steps I posted will do that.
Make sure you're selecting Blanks after clicking Special.

 

[vbanoob1234]

[TABLE="width: 540"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD] input vlookup formula [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Subtotal [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1 thing[/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] input vlookup formula [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] input vlookup formula [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Subtotal [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2 things[/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] input vlookup formula [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] input vlookup formula [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] input vlookup formula [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] blank [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Subtotal [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3 things[/TD]
[/TR]
</tbody>[/TABLE]