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]

Sorry ...

Back to your original question ...

Is there something preventing you from deleting the Blank row in-between your two groups ?

 

[Grunter31]

Sorry for the extremely late reply just had my first Baby (a little Boy)

The Blank cells on Sheet 2 can be removed. But Nothing on Sheet 1 can be changed except the Cell with "Select Opposition"

Sheet 2 is auto updated with the daily fixture.

I would like a formula to match the relevant team on Sheet 1 (in the Cell that reads "Select Poosition) with who they are playing from the fixture on Sheet 2.

Sheet 1

A1	Team
A2
A3	No Game
A4	Select Opposition
A5	Atlanta Hawks
A6	Atlanta Hawks
A7	Atlanta Hawks
A8	Atlanta Hawks
A9	Atlanta Hawks
A10
A11
A12
A13
A14	No Game
A15	Select Opposition
A16	Boston Celtics
A17	Boston Celtics
A18	Boston Celtics
A19	Boston Celtics
A20	Boston Celtics
A21
A22
A23
A24	No Game
A25	Select Opposition
A26	Brooklyn Net

<tbody>
</tbody>

Sheet 2

A33	Charlotte Hornets
A34	Brooklyn Nets
A35
A36	Atlanta Hawks
A37	Washington Wizards
A38
A39	Minnesota Timberwolves
A40	Boston Celtics

<tbody>
</tbody>

 

[James006]

First Things First ... CONGRATULATIONS ...!!!!

 

[James006]

With your latest lay-out of your spreadsheets ...

You could test following :

=INDEX(Sheet2!$A$33:$A$40,CHOOSE(MATCH(A5,Sheet2!$A$33:$A$40,0),2,1,,5,4,,8,7))

Hope this will help .. :wink:

 

[Grunter31]

Thanks for the words of congratualtions

 

[Grunter31]

The formula worked brilliantly. I had to go down another 27 teams but I worked out what you did.

Thanks again for your time and patience.

 

[James006]

Glad you could solve your problem ... :wink:

AND

Enjoy as much as possible ... all the great moments that parenthood will offer you ...!!!