If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complex IF THEN statement
HI...I'm trying to figure out how to do a long IF THEN statement. I have
three columns. Column 1 is validated data with 20 possibilities. Column two is validated data with 15 possibilities. I want the third column to automatically fill in based on the values in columns 1 & 2. I think if someone can solve the following example, where the third column is filled in based on the combination of 1 & 2 which are manually entered, I could modify it to match what I need: 1 2 3 Cheap Lunch Restaurant 1 Cheap Dinner Restaurant 2 Expensive Dinner Restaurant 3 Expensive Brunch Restaurant 3 Expensive Lunch Restaurant 4 Cheap Brunch Restaurant 5 As you can see, there are no calculations involved, only manual entry of data. |
#2
|
|||
|
|||
Complex IF THEN statement
Greg S Greg wrote...
HI...I'm trying to figure out how to do a long IF THEN statement. *I have three columns. *Column 1 is validated data with 20 possibilities. *Column two is validated data with 15 possibilities. *I want the third column to automatically fill in based on the values in columns 1 & 2. * .... How would the 3rd column automatically fill in based on the 1st and 2nd columns? By looking up the 1st and 2nd column values in a 3-column table elsewhere? By generating different labels in the 3rd column for each distinct combination of entries in the 1st and 2nd columns? Something else? How were the Restaurant # entries in the 3rd column of your sample table determined? Were they just more entries along with the 1st and 2nd columns? If so, it'd seem you want to perform table lookups. If your source table were named TBL and you the 1st and 2nd column values you wanted to look up were in cells C5 and D5, you could use an array formula like =INDEX(SRC,MATCH(1,(INDEX(SRC,0,1)=C5)*(INDEX(SRC, 0,2)=D5),0),3) On the other hand, if you wanted to generate unique labels consisiting of the word Restaurant followed by a space followed by a different number for each combination of entries in the 1st and 2nd columns, and if the top-left cell of the table were A1, you could use formulas like C1: Restaurant 1 C2: =IF(COUNT(MATCH(1,COUNTIF(A2,A$1:A1)*COUNTIF(B2,B$ 1:B1),0)), INDEX(C$1:C1,MATCH(1,COUNTIF(A2,A$1:A1)*COUNTIF(B2 ,B$1:B1),0)), "Restaurant "&(MAX(--SUBSTITUTE(C$1:C1,"Restaurant ",""))+1)) Fill C2 down in column C as far as needed. |
#4
|
|||
|
|||
Complex IF THEN statement
My suggestion would be to create a table in a separate--hidden if you
like--sheet with the 20 options down the rows and the 15 options across the columns with all the data items filled in in tabular format. You will need to create lookup data regardless of what formula you want to use. If you use my suggested format for the lookup data, then the formula in C2 would be: =index(Table!$A$1:$P$21,match(A2,Table!$A$1:$A$21, 0),match(B2,Table!$A$1:$P$1,0) "Greg S" wrote: HI...I'm trying to figure out how to do a long IF THEN statement. I have three columns. Column 1 is validated data with 20 possibilities. Column two is validated data with 15 possibilities. I want the third column to automatically fill in based on the values in columns 1 & 2. I think if someone can solve the following example, where the third column is filled in based on the combination of 1 & 2 which are manually entered, I could modify it to match what I need: 1 2 3 Cheap Lunch Restaurant 1 Cheap Dinner Restaurant 2 Expensive Dinner Restaurant 3 Expensive Brunch Restaurant 3 Expensive Lunch Restaurant 4 Cheap Brunch Restaurant 5 As you can see, there are no calculations involved, only manual entry of data. |
#5
|
|||
|
|||
Complex IF THEN statement
Assume you have the base reference listing in Sheet2's A1:C10
In Sheet1, you have listed the twin inputs in A2:B2 down, eg: Cheap, Dinner Put in C2, normal ENTER: =INDEX(Sheet2!$C$1:$C$10,MATCH(1,INDEX((Sheet2!$A$ 1:$A$10=A2)*(Sheet2!$B$1:$B$10=B2),),0)) Copy C2 down as far as required. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Greg S" wrote: HI...I'm trying to figure out how to do a long IF THEN statement. I have three columns. Column 1 is validated data with 20 possibilities. Column two is validated data with 15 possibilities. I want the third column to automatically fill in based on the values in columns 1 & 2. I think if someone can solve the following example, where the third column is filled in based on the combination of 1 & 2 which are manually entered, I could modify it to match what I need: 1 2 3 Cheap Lunch Restaurant 1 Cheap Dinner Restaurant 2 Expensive Dinner Restaurant 3 Expensive Brunch Restaurant 3 Expensive Lunch Restaurant 4 Cheap Brunch Restaurant 5 As you can see, there are no calculations involved, only manual entry of data. |
#6
|
|||
|
|||
Complex IF THEN statement
I think I would combine columns A and B into column A on a second sheet.
In column B of sheet2 enter the Restaurants On sheet1 in C1 enter =VLOOKUP(A1 & " " & B1,Sheet2!$A$1:$B$6,2,FALSE) Or just enter the data in one cell on sheet1 as cheap lunch then use this formula in B1 =VLOOKUP(A1,Sheet2!$A$1:$B$6,2,FALSE) Gord Dibben MS Excel MVP On Mon, 8 Dec 2008 13:02:01 -0800, Greg S Greg wrote: HI...I'm trying to figure out how to do a long IF THEN statement. I have three columns. Column 1 is validated data with 20 possibilities. Column two is validated data with 15 possibilities. I want the third column to automatically fill in based on the values in columns 1 & 2. I think if someone can solve the following example, where the third column is filled in based on the combination of 1 & 2 which are manually entered, I could modify it to match what I need: 1 2 3 Cheap Lunch Restaurant 1 Cheap Dinner Restaurant 2 Expensive Dinner Restaurant 3 Expensive Brunch Restaurant 3 Expensive Lunch Restaurant 4 Cheap Brunch Restaurant 5 As you can see, there are no calculations involved, only manual entry of data. |
Thread Tools | |
Display Modes | |
|
|