Problem with Multiple OR Statements in Formula

[stusic]

Hey All,

I'm having issues with a formula. I've narrowed it down to the 3rd IF statement in the VLOOKUP section, specifically the OR statement.

Here is the formula:

Please Login or Register  to view this content.

The problem lies with N8, which is a letter grabbed from another string. If I change this to "R" or "M", it works fine. If I use any of the other letters ("A"-"J"), it resolves to "-", which is the ISERROR resolution.

Can someone take a look and tell me what I'm doing wrong? AFAIK, I can nest a bunch of variable in an OR statement, so I'm not sure what's wrong.

Thanks

[arlu1201]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[stusic]

Is there a term for when you find an answer immediately after you ask the question? It's almost as if the act of asking is what triggers the realization of the solution. Anyways, I figured it out; I couldn't simply put one OR statement, I had to dole out an OR statement for each possibility as such:

Please Login or Register  to view this content.

Thanks All,

-stu

[ROHAN999]

What is your req/result

descrb your data ,then req/result

[ROHAN999]

=if(iserror(vlookup(o8,$l$35:$p$45,if(n8="r",3,if(n8="m",4,if(or(n8="a",n8="b",n8="c",n8="d",n8="e",n8="f",n8="g",n8="h",n8="j"),5,"unknown"))))),"-",vlookup(o8,$l$35:$p$45,if(n8="r",3,if(n8="m",4,if(or(n8="a",n8="b",n8="c",n8="d",n8="e",n8="f",n8="g",n8="h",n8="j"),5,"unknown")))))

Please Login or Register  to view this content.

[ROHAN999]

That is what i was suggesting

[ROHAN999]

No prob your issue gets resolve is imp

[stusic]

That is what i was suggesting

Yeah, I think we're both on the same page. Thanks for responding.

[martindwilson]

the IF bit should be like this
=IF(N9="R",3,IF(N9="M",4,IF(OR(N8={"A","B","C","D","E","F","G","H","J"}),5,"unknown")))
but if the answer is unknown the vlookup wouldnt work as there is no such column number as unknown!also the vlookup should have FALSE at the end
=VLOOKUP(O9,$L$35:$P$45,IF(N9="R",3,IF(N9="M",4,IF(OR(N8={"A","B","C","D","E","F","G","H","J"}),5,"unknown"))),FALSE)