Two criteria logic statment

[chriswrcg]

I have a logic statement that checks to see if a cell contains a word. and the adjacent cell contains a number. depending on if the word is correct and what number is selected, another cell populates with another number. My formula works but I am having trouble adding another layer to it.
The selection numbers will always be a 1 through 5 selection while the word will be yes or no.

Yes 1 would return 6
yes 2 would return 7 and so on.

I want to add another if statement to display another set of numbers when the word changes from yes to no.

No 1 would return 11
No 2 would return 12

And then another set when changed to a 3rd word

Maybe 1 would return 16
Maybe 2 would return 17

When I try to add the "No" logic statement to the formula it woks as long as the word is yes but as soon as I change the word to no it returns false since the formula recognizes that the first of the statement (when we were checking for the word yes) is false so it returns false.

How do I get it to recognize the word change and what to do instead of defaulting to false?

[Glenn Kennedy]

One way:

VLOOKUP(O4,INDEX((J4:K8,J10:K14),,,MATCH(N4,{"yes","no"},0)),2,FALSE)

[chriswrcg]

That is the way

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.