Complex IF THEN statement

[Greg S]

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.

 

[Harlan Grove]

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.

 

[Bob Phillips]

Create a table with all variations of 1 & 2 concatenated and its appropriate
3, e.g.

CheapLunch Restaurant 1
CheapDinner Restaurant 2
ExpensiveDinner Restaurant 3
ExpensiveBrunch Restaurant 3
etc.

and then use a lookup such as

=VLOOKUP(A2&B2,M1:N100,2,False)

 

[~L]

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)

 

[Max]

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

 

[Gord Dibben]

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