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

[TABLE="width: 231"]
<tbody>[TR]
[TD]A1[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]No Game[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Select Opposition[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]Atlanta Hawks[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]Atlanta Hawks[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]Atlanta Hawks[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]Atlanta Hawks[/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD]Atlanta Hawks[/TD]
[/TR]
[TR]
[TD]A10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A14[/TD]
[TD]No Game[/TD]
[/TR]
[TR]
[TD]A15[/TD]
[TD]Select Opposition[/TD]
[/TR]
[TR]
[TD]A16[/TD]
[TD]Boston Celtics[/TD]
[/TR]
[TR]
[TD]A17[/TD]
[TD]Boston Celtics[/TD]
[/TR]
[TR]
[TD]A18[/TD]
[TD]Boston Celtics[/TD]
[/TR]
[TR]
[TD]A19[/TD]
[TD]Boston Celtics[/TD]
[/TR]
[TR]
[TD]A20[/TD]
[TD]Boston Celtics[/TD]
[/TR]
[TR]
[TD]A21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A24[/TD]
[TD]No Game[/TD]
[/TR]
[TR]
[TD]A25[/TD]
[TD]Select Opposition[/TD]
[/TR]
[TR]
[TD]A26
[/TD]
[TD]Brooklyn Net[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="width: 231"]
<tbody>[TR]
[TD]A33[/TD]
[TD]Charlotte Hornets[/TD]
[/TR]
[TR]
[TD]A34[/TD]
[TD]Brooklyn Nets[/TD]
[/TR]
[TR]
[TD]A35[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]A36[/TD]
[TD]Atlanta Hawks[/TD]
[/TR]
[TR]
[TD]A37[/TD]
[TD]Washington Wizards[/TD]
[/TR]
[TR]
[TD]A38[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]A39[/TD]
[TD]Minnesota Timberwolves[/TD]
[/TR]
[TR]
[TD]A40[/TD]
[TD]Boston Celtics[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 ...!!!