Question abotu IF function

[kzhou21]

Hi everyone,
I am new and have a quick question regarding excel. I have combinations where I want it so that if you type "June 2011" in one cell and "Live", you get automated get the code "r1." I would like to do this for combinations of "June 2011" and "Staged" to get "f1," Sept 2011" and "Live" to get "u1", "Sept 2011" and "Staged" to get "i1", "Dec 2011" and "Live" to get "x1, "Dec 2011" and "Staged" to get "l1", March 2012" and "Live" to get "o2", "March 2012" and "Staged to get "c2", "June 2012" and "Live" to get "r2," and "June 2012 and "Staged" to get "f2". Is there any way that I could include this in a single "if" function so that any combination I type separtely should produce the code I am looking for? Thanks in advance.

[Bob Phillips]

Create a table of all concatenated combinations and result values and use VLOOKUP

=VLOOKUP(A2&B2,lookup_table,2,False)

[Roger Govier]

Hi

take a look at the attached file.
I set up a list of your data on sheet2 and then defined some dynamic ranges, which will grow if you add more data.

Then on sheet1, I placed Data Validation dropdowns in cells A2 and B2 and used an array entered Index match formula to return the result.

To enter or amend an Array formula, use Control + Shift + Enter (CSE) instead of just Enter. Do not type the curly braces { } yourself. When you use CSE, Excel will enter them for you.

Please Login or Register  to view this content.