IF / OR Statement

Hi,
I need a little help with If / Or statements.
I have a cell in a sheet (lets call it B4) that will auto populate based on cell B2.
B2 is a drop down in another sheet call "Lookup Values"

My statement is:
=IF(OR(B4='Look Up Values'!Q9,'Look Up Values'!Q10,'Look Up Values'!Q11,'Look Up Values'!Q12,'Look Up Values'!Q16,'Look Up Values'!Q17),1,(OR(B4='Look Up Values'!Q13,'Look Up Values'!Q14,'Look Up Values'!Q15),2,0))


if I make the below change (adding an if, in Bold Red), it will only return the correct value for the first item in each OR statement otherwise will give me a return of 0

=IF(OR(B4='Look Up Values'!Q9,'Look Up Values'!Q10,'Look Up Values'!Q11,'Look Up Values'!Q12,'Look Up Values'!Q16,'Look Up Values'!Q17),1,if(OR(B4='Look Up Values'!Q13,'Look Up Values'!Q14,'Look Up Values'!Q15),2,0))

Any help is appreciated.

Cheers BJS

Quote:

---

=IF(OR(B4='Look Up Values'!Q9,'Look Up Values'!Q10,'Look Up Values'!Q11,'Look Up Values'!Q12,'Look Up Values'!Q16,'Look Up Values'!Q17),1,(OR(B4='Look Up Values'!Q13,'Look Up Values'!Q14,'Look Up Values'!Q15),2,0))

---

If(B4 = Any,1,B4 = Any,2,0))

Code:

---

If B4 = Any Then
1
Else
True Or False Then 'this is the second OR Function
2,0) ' I dunno what these 4 characters will do, they don't belong
End If

---

Quote:

---

=IF(OR(B4='Look Up Values'!Q9,'Look Up Values'!Q10,'Look Up Values'!Q11,'Look Up Values'!Q12,'Look Up Values'!Q16,'Look Up Values'!Q17),1,if(OR(B4='Look Up Values'!Q13,'Look Up Values'!Q14,'Look Up Values'!Q15),2,0))

---

=IF(B4= any,1,if(B4=any,2,0))

Code:

---

If B4 = Any then
1
Else
  If B4 = Any Then
2
Else
0
End If
Else
' = empty or 0 depending on Cell format
End If

---

So, what is the question?

Watch what happens if you change the second formula to

Quote:

---

=IF(OR(B4='Look Up Values'!Q9,'Look Up Values'!Q10,'Look Up Values'!Q11,'Look Up Values'!Q12,'Look Up Values'!Q16,'Look Up Values'!Q17),1,IF(OR(B4='Look Up Values'!Q13,'Look Up Values'!Q14,'Look Up Values'!Q15),2,0)"SamT at VBAX")

---

Hi SamT
thanks. you got the brain thinking.
I added an additional column to the look up values page and turned it into a Vlookup.

Works well. thanks