Lookup, value could be in more than one column

[Davg]

I have the results of a load of basketball games and want to find out what a team's last result was. (I got columns C and D with simple if functions on the results and these columns may be redundant.)

So in E2 I would like to have a lookup function which searches columns A and B for the first occurrence of "Golden State Warriors" and then returns if they won or not. i.e. in this case it will come out as "no"

Many thanks for the help and sorry this is badly explained.

<table width="750" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:10898; width:224pt" span="2" width="298"> <col style="mso-width-source:userset;mso-width-alt:2816; width:58pt" span="2" width="77"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:224pt" height="20" width="298">Home Team</td> <td style="width:224pt" width="298">Away Team</td> <td style="width:58pt" width="77">Home win?</td> <td style="width:58pt" width="77">Away win?</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Golden State Warriors </td> <td> Portland Trailblazers</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Los Angeles Clippers </td> <td> Memphis Grizzlies</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Phoenix Suns </td> <td> San Antonio Spurs</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Charlotte Bobcats </td> <td> Atlanta Hawks</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Chicago Bulls </td> <td> New Jersey Nets</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Cleveland Cavaliers </td> <td> Washington Wizards</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Dallas Mavericks </td> <td> New Orleans Hornets</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Philadelphia 76ers </td> <td> Detroit Pistons</td> <td>No</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Toronto Raptors </td> <td> Miami Heat</td> <td>No</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Utah Jazz </td> <td> Denver Nuggets</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Los Angeles Lakers </td> <td> San Antonio Spurs</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Portland Trailblazers </td> <td> Memphis Grizzlies</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New York Knicks </td> <td> Chicago Bulls</td> <td>No</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Sacramento Kings </td> <td> Oklahoma City Thunder</td> <td>No</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Denver Nuggets </td> <td> Golden State Warriors</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Houston Rockets </td> <td> Dallas Mavericks</td> <td>No</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Milwaukee Bucks </td> <td> Toronto Raptors</td> <td>Yes</td> <td>No</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New Orleans Hornets </td> <td> Utah Jazz</td> <td>No</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Detroit Pistons </td> <td> Cleveland Cavaliers</td> <td>No</td> <td>Yes</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Atlanta Hawks </td> <td> Miami Heat</td> <td>No</td> <td>Yes</td> </tr> </tbody></table>

 

[taurean]

Sorry I can't add to previous post. A variation will work if the previous one works.

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F3</TD><TD>{=IF(INDIRECT("A"&LARGE(IF(A3:B22=E3,ROW(A3:B22)),1))=E3,INDIRECT("C"&LARGE(IF(A3:B22=E3,ROW(A3:B22)),1)),INDIRECT("D"&LARGE(IF(A3:B22=E3,ROW(A3:B22)),1)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

 

[silkfire]

If a team is in the Home team column the formula should look for a result in the Home won? column, right?

 

[Marcelo Branco]

That's not working either...

For home teams it returns their last home result and for away teams it returns their last away result. I need to it return their last result regardless of home or away. Sorry I can't help with this, it is way beyond what I can do. Thanks

For me it worked perfectly.

For example: for Golden State Warriors (Home Team in row 2) the formula returns No from row 16 (Away team)

The same for Portland Trailblazers (Away team in row 2) and the formula returns Yes from row 13 (Home team)

M.

 

[Davg]

Marcel,

i don't know why it's not working for we. but in my spreadsheet which has a lot more games, it is picking up the wrong one. e.g. There is another Golden State entry in A28 and the formula is finding this value.

taurean,

Thanks! it's so close to working. The only problem is the formula picks up the game in the same row. i.e. if in E3 you had Golden State Warriors (which is what i'll need to do) it picks up cell C3.

Thanks again.

 

[Marcelo Branco]

Davg,

Maybe it's a layout-issue.
Just for confirmation:
Data in A1:B? (headers in row 1)
Lookup value(s) cell(s)?
Formula cell?
Are you copying down the formula or just putting it on one cell?

M.

 

[taurean]

Marcel,

i don't know why it's not working for we. but in my spreadsheet which has a lot more games, it is picking up the wrong one. e.g. There is another Golden State entry in A28 and the formula is finding this value.

taurean,

Thanks! it's so close to working. The only problem is the formula picks up the game in the same row. i.e. if in E3 you had Golden State Warriors (which is what i'll need to do) it picks up cell C3.

Thanks again.

Davg,

Maybe I've understood your requirement this time around. Insert the following ARRAY (Ctrl + Shift + Enter) formula in E3 and then copy down:
=IF(ISNUMBER(LARGE(IF(A4:A22=E3,ROW(A4:A22)),1))>ISNUMBER(LARGE(IF(B4:B22=E3,ROW(B4:B22)),1)),INDIRECT("C"&LARGE(IF(A4:A22=E3,ROW(A4:A22)),1)),INDIRECT("D"&LARGE(IF(B4:B22=E3,ROW(B4:B22)),1)))

 

[Matty]

Adapting taurean's suggestion slightly, does this do as desired:

=IF(AND(MIN(IF(A5:A$21=F4,ROW(A5:A$21))) > 0,MIN(IF(A5:A$21=F4,ROW(A5:A$21))) < MIN(IF(B5:B$21=F4,ROW(B5:B$21)))),INDIRECT("C"&MIN(IF(A5:A$21=F4,ROW(A5:A$21)))),INDIRECT("D"&MIN(IF(B5:B$21=F4,ROW(B5:B$21)))))

<min(if(b5:b$21=f4,row(b5:b$21)))),indirect("c"&min(if(a5:a$21=f4,row(a5:a$21)))),indirect("d"&min(if(b5:b$21=f4,row(b5:b$21)))))[ code]<min(if(b5:b$21="f4,row(b5:b$21)))),indirect("c"&min(if(a5:a$21=f4,row(a5:a$21)))),indirect("d"&min(if(b5:b$21=f4,row(b5:b$21)))))<br"><min(if(b3:b$21=f2,row(b3:b$21)))),indirect("c"&min(if(a3:a$21=f2,row(a3:a$21)))),indirect("d"&min(if(b3:b$21=f2,row(b3:b$21)))))[ code]which="" requires="" committing="" with="" ctrl+shift+enter.

Which requires committing with CTRL+SHIFT+ENTER.

It assumes your table occupies range A1:D21.

Matty</min(if(b3:b$21=f2,row(b3:b$21)))),indirect("c"&min(if(a3:a$21=f2,row(a3:a$21)))),indirect("d"&min(if(b3:b$21=f2,row(b3:b$21)))))[></min(if(b5:b$21=f4,row(b5:b$21)))),indirect("c"&min(if(a5:a$21=f4,row(a5:a$21)))),indirect("d"&min(if(b5:b$21=f4,row(b5:b$21)))))[>

 

[Davg]

taurean,

I pasted that formula into E3 like you said. It warned me about circular references (I clicked OK), but every time it just comes out as 0 in column E

Marcelo,

The data is in columns A and B with headers in row 1.
I don't know what you mean by
"Lookup value(s) cell(s)?
Formula cell?"
and I copied the formula down after pasting it in.

Matty,

I tried pasting that into several different cells and also having another column for names like taurean's formula, but it just comes out with #VALUE!

 

[Davg]

Is there a way I can upload the file so you can just look at it?

 

[Matty]

Matty,

I tried pasting that into several different cells and also having another column for names like taurean's formula, but it just comes out with #VALUE!

Did you commit the formula with CTRL+SHIFT+ENTER as advised?

Matty