Formula to return value if cells above and below are populated

[Mr2017]

Hi

I’ve got a vlookup in one sheet that looks up data from another sheet.

However, there are a number of instances where there is no matching data, so the formula has been written to return a blank instead of an error.

But I’d like to to create a formula which returns the value of the cell below the active cell IF the value of the cells above and below the active cell are the same

Eg

If cell A1 had the word “Cool”

Cell A2 was blank and

Cell A3 had the word “Cool”

I’d want the value of cell A2 to be “Cool.”

Has anyone done this before?

TIA

 

[Is_REF]

Just add the IF condition, something like:

=if[and(vlookup(...)="", A1=A3),A1, vlookup(...)]

I hope it helps!

 

[Mr2017]

Ok, cool!

Thanks - I’ll try that out tomorrow!

 

[Mr2017]

Thanks - I initially got a circular reference error when I tried to combine all the info in one formula, but I added in a new column and was able to create a formula which did what I wanted using the elements of what you described above.

Thanks again for your response.