Formula

[BKG SPORTS]

Here's what i'm trying to do:

a1=kent st
a2=alabama
b1=24
b2=37

in another part of this work sheet i want to find out who alabama played

f1=alabama
g1=the formula, which i can't come up with

Thanks for any help
BKG

 

[shg]

=index(a:a, match(f1, a:a, 0) - 1)

 

[T. Valko]

Here's what i'm trying to do:

a1=kent st
a2=alabama
b1=24
b2=37

in another part of this work sheet i want to find out who alabama played

f1=alabama
g1=the formula, which i can't come up with

Thanks for any help
BKG

If the opponent will ALWAYS appear above the other team then you can look for Alabama and offset its location by one.

However, if you want to know the opposite, who Kent ST. played, then this presents a problem!

So, we'd need a better explanation of how the data is setup.

 

[BKG SPORTS]

<TABLE style="WIDTH: 270pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=360><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3360" width=105><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1376" span=3 width=43><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 1824" width=57><TBODY><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 79pt; HEIGHT: 17.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=23 width=105>KENT ST</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=69>MAC</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=43> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=43> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=43> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=57>24</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=23>ALABAMA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>SEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>-29</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>44</TD></TR></TBODY></TABLE>


In alabama's log: i want it to show who it played, which is kent st
then in kent st's log it would show who it played which is alabama

 

[T. Valko]

<TABLE style="WIDTH: 270pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=360 border=0><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3360" width=105><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1376" span=3 width=43><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 1824" width=57><TBODY><TR style="HEIGHT: 17.25pt" height=23><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 79pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent" width=105 height=23>KENT ST</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=69>MAC</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 32pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=43></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 32pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=43></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 32pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=43></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 43pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=57>24</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent" height=23>ALABAMA</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SEC</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-29</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">55</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">44</TD></TR></TBODY></TABLE>


In alabama's log: i want it to show who it played, which is kent st
then in kent st's log it would show who it played which is alabama

Let me try explaining it like this...

If we want to know who Kent ST played how do we know to look below Kent ST and, by the same token, if we want to know who Alabama played how do we know to look above Alabama?

 

[BKG SPORTS]

That's what I'm trying to figure out.
I guess it won't work like that.
Any other suggestions

Thanks
BKG

 

[T. Valko]

That's what I'm trying to figure out.
I guess it won't work like that.
Any other suggestions

Thanks
BKG

This is a perfect example of how the spreadsheet design can make things either easy or not so easy!

Unless you can provide some more specific details as to how the data is setup I'm affraid there's not much more I can suggest.