Formula to bring through data in cell above VLOOKUP Value

[Declamatory]

Hi Folks,

I hope I can explain this OK.

I have a VLOOKUP that brings through data in an array. The cell either above or below the value will always blank. I need a formula that if the cell above the lookup value is blank then bring through the value in the cell below the lookup value and if it is not blank then bring through the value in the cell above the lookup value.

Example

John is partnered with another person (David). I don't know if John will appear above or below his partner in the list but there will always be a blank line in between partners. I need a formula that does a VLOOKUP or similar to look at the people in column D and see who they were partnered with. So if the cell above A4 is blank bring through the value in A5 but if the cell above A4 is not blank then bring through the value in A3.

Hope that makes sense


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Philip[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Formula to bring through Philip[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Formula to bring through "David"[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Excel 2010
	A	B	C	D	E
1	Philip
2	Peter
3
4	John
5	David			Philip	Peter
Sheet22

Cell Formulas
Range	Formula
E5	=LOOKUP("ZZZZZZZZZZZ",INDEX($A$1:$A$33,N(IF(1,MATCH(D5,$A$1:$A$33,0)+{-1,1}))))

 

[becci.gott]

Slightly complicated as you have the partners starting at the top with no headers etc (so it can't actually check for a value in A0)... but this works:

=IF(A1="","",IF(IFERROR(OFFSET(A1,-1,0)="",TRUE),OFFSET(A1,1,0),OFFSET(A1,-1,0)))

 

[Declamatory]

Hi VBA Geek,

Thanks for the response. Unfortunately I may have over simplified my initial query.

When I said I had a LOOKUP that was looking at column A it was actually looking at an array (Wonned) in another worksheet.

=VLOOKUP(TEXT(A3,"dd/mm/yyyy")&" "&$J$2&" "&$A$1,Wonned,4,FALSE)

A3 is the date the match took place. J2 is the type of game and A1 is the player. The first column of the array would appear in column C of a sheet called Results and the value would look like "12/09/2017 Doubles Philip". Column F is the list of players and column 4 of the array.

The LOOKUP would bring through Philip. Are you able to incorporate that into your formula? I just can't seem to make it work.

Thanks,

 

[VBA Geek]

maybe post a sample of your data and expected results? use mr excel html maker if needed: https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html