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>

 

[Matty]

That was the reason for the using of 0000 instead of 00. I saw no reason to spare 0 (zeros), but you are right in the sense that the formula with less zeros is clearer and easier to understand.

It was a good shout, Marcelo. And it seems that INDIRECT understands the address regardless of how many 0s are used.

Thanks for sharing this.

Matty