Formula IF(OR

[Jon Johny]

I have a formula construction question. Below is the formula I have. (I am looking up the number of wins a certain team has). The lookup is looking this team up on the 'Team1' range, however, sometimes the team I am looking up may not be in this range and on the 'Team2' range, or in the 'Team3' range. How do I incorporate Team2 and Team3 ranges into this formula??

I think this is IF(OR(

Thank you

=IF(ISERROR(VLOOKUP($A3,Team1,2,FALSE)),"0",VLOOKUP($A3,Team1,2,FALSE))

 

[Andrew Poulsom]

Try:

=IF(COUNTIF(Team1,$A3),VLOOKUP($A3,Team1,2,FALSE),IF(COUNTIF(Team2,$A3),VLOOKUP($A3,Team2,2,FALSE),IF(COUNTIF(Team3,$A3),VLOOKUP($A3,Team3,2,FALSE),0)))

 

[Jon Johny]

Great, thanks. Can you include a modifier, like I had in the above formual that accomodate errors?

Thanks

 

[schielrn]

This is one way:

=IF(ISERROR(VLOOKUP($A3,Team1,2,FALSE)),IF(ISERROR(VLOOKUP($A3,Team2,2,FALSE)),IF(ISERROR(VLOOKUP($A3,Team3,2,FALSE)),0,VLOOKUP($A3,Team3,2,FALSE)),VLOOKUP($A3,Team2,2,FALSE)),VLOOKUP($A3,Team1,2,FALSE))

I hope the parentheses are correct.

Hope that helps.

 

[Andrew Poulsom]

Great, thanks. Can you include a modifier, like I had in the above formual that accomodate errors?

Thanks

I don't think you will get an error with the formula I posted. It uses COUNTIF to check that the lookup value exists.

 

[xld]

Try

=IF(NOT(ISERROR(VLOOKUP($A3,Team1,2,FALSE))),VLOOKUP($A3,Team1,2,FALSE),
IF(NOT(ISERROR(VLOOKUP($A3,Team2,2,FALSE))),VLOOKUP($A3,Team2,2,FALSE),
IF(NOT(ISERROR(VLOOKUP($A3,Team3,2,FALSE))),VLOOKUP($A3,Team3,2,FALSE),0)))

 

[Jon Johny]

Andrew, the thing is I am counting, Text entries, no numbers. Does that change it?

 

[Andrew Poulsom]

Why don't you try it?

 

[Jon Johny]

This formula worked out great...I just need this formula when team 3 is not included. I simply deleted the last condition (team3) but I got an N/A error. Im assuming Im missing some brakcet or other...

How do I do this?


=IF(ISERROR(VLOOKUP($A3,Team1,2,FALSE)),IF(ISERROR(VLOOKUP($A3,Team2,2,FALSE)),IF(ISERROR(VLOOKUP($A3,Team3,2,FALSE)),0, VLOOKUP($A3,Team3,2,FALSE)),VLOOKUP($A3,Team2,2,FALSE)),VLOOKUP($A3,Team1,2,FALSE))

 

[Aladin Akyurek]

I have a formula construction question. Below is the formula I have. (I am looking up the number of wins a certain team has). The lookup is looking this team up on the 'Team1' range, however, sometimes the team I am looking up may not be in this range and on the 'Team2' range, or in the 'Team3' range. How do I incorporate Team2 and Team3 ranges into this formula??

I think this is IF(OR(

Thank you

=IF(ISERROR(VLOOKUP($A3,Team1,2,FALSE)),"0",VLOOKUP($A3,Team1,2,FALSE))

Also, because you expect a number...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4},
   0,
   VLOOKUP($A3,Team1,2,0),
   VLOOKUP($A3,Team2,2,0),
   VLOOKUP($A3,Team3,2,0)))