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

 

[vbanoob1234]

Hi Jonmo1

Thanks for your quick response. No unfortunately not. That is how the format looks like:

[TABLE="width: 540"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD] highlight blank cell (want a vlookup) [/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 - sum formula [/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] highlight blank cell (want a vlookup) [/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] highlight blank cell (want a vlookup) [/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 - sum formula [/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] highlight blank cell (want a vlookup) [/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] highlight blank cell (want a vlookup) [/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] highlight blank cell (want a vlookup) [/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 - sum formula [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3 things[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

In that case the method I've described won't work, it will select ALL the blank cells, not just the blank AND highlighted cells.

HOW are the cells highlighted?
Are they yellow via conditional formatting?
Or just randomly selecting a few cells and using standard cell fill color?

 

[vbanoob1234]

Hi Jonmo1

It is just selecting a few lines, and using standard cell fill colour. thanks

 

[Jonmo1]

It is just selecting a few lines, and using standard cell fill colour. thanks

Why not put the vlookup in 'during' your process of selecting which cells to highlight by hand?
Once you pick the first cell to highlight...
Put in the formula for that cell
Make sure to use appropriate absolute referencing in that formula.
i.e. =VLOOKUP(A1,$A:$Z,2,FALSE)
Highlight it yellow
COPY that cell

Now each cell you choose to highlight, just right click / paste.