Lookup, Where Cell/Selection needed could be in Cell above or below Lookup

[Grunter31]

I'm trying to set up a daily fixture.

On Sheet 2 I have set up a web query to bring in the daily fixture. It sits in Column A, Row 33 - 76. Its in groups of 2 with a blank cell between each group.

On Sheet 1 is where all the relevant formulas are.

Its impossible to get the web query in Sheet 1 (even though that would solve my issue but it can't be done)

So what I'm trying to do is on Sheet 1

A15 Selection A
A16 Blank Cell

A30 Selection B
A31 Blank Cell

I need to fill "Blank Cell" from the Daily Fixture.

I've tried Vlookup but the selection on Sheet 2 I need could be above or below the Lookup. There are blank cells between the 2 selection on Sheet 2.

 

[James006]

Hello,

As an initial step, have you tried to use the match() function to locate the row number in Sheet 2 ?

 

[Grunter31]

I've tried, VLookup, Match, Index etc. I can get formula to work but only 1 way. Either below the lookup cell or above the lookup cell.
So basically what I need to do is Find referenced cell and look below if there is a name in that cell great use that cell, if the cell is Blank then look above the referenced cell.

It may well be a simple Match Index and I'm missing something.

 

[James006]

Quite strange ...

Are you dealing with Merged cells ???

 

[Grunter31]

Nothing.

 

[Grunter31]

It's very strange. did you have a formula to work it out.

 

[James006]

You will admit ... that without seeing your worksheet ... the challenge becomes extremely difficult ...!!! :wink:

 

[Grunter31]

Sorry if I did I didn't mean to sound rude. At the moment not near my laptop just on phone.

If you place selection A in Sheet 1 Cell A4 and on Sheet 2 place Selection A in Cell A10 and Selection B in Cell A11, Selection C in Cell A13 and Selection D in Cell A14.

I need Selection B from Sheet 2 to show up in Sheet 1 Cell C5

The problem is that in Sheet 2 selections ABCD all could be matched against each other in any order.

 

[James006]

Hello again,

In your Sheet1 in cell C5, you could test following:

=OFFSET(INDEX(Sheet2!A1:A14,MATCH(A4,Sheet2!A1:A14,0)),1,0)

The pending question is the Offset Value ...1 ...

What could help you determine this value ...?

Hope this will help

 

[James006]

What about a second proposal ...

=OFFSET(INDEX(Sheet2!A10:A14,MATCH($A$4,Sheet2!A10:A14,0)),IF(MATCH($A$4,Sheet2!A10:A14,0)=1,MATCH($A$4,Sheet2!A10:A14,0),4-MATCH($A$4,Sheet2!A10:A14,0)),0)

Hope this will help