Hello - I've searched, but cannot find what I'm looking for... I need to do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

=IF(AND(A1="red",A2="blue"),10,IF(AND(A1="green",A 2="orange"),20,IF(AND(A2="purple",A2="yellow"),30, "")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Shelly" wrote in message
...
Hello - I've searched, but cannot find what I'm looking for... I need to
do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

One way:

=IF(AND(A1="red",A2="blue"),10,IF(AND(A1="green",A 2="orange"),20,IF(AND(A1=purple",A2=yellow"),30,0) ))

You didn't say what you want if *none* of those conditions are met so the
formula will return 0.

Biff

"Shelly" wrote in message
...
Hello - I've searched, but cannot find what I'm looking for... I need to
do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

=(A1="red")*(A2="blue")*10+(A1="green")*(A2="orang e")*20+(A1="purple")*(A2="yellow")*30


no IFs are needed.
--
Gary's Student
gsnu200706


"Shelly" wrote:

Hello - I've searched, but cannot find what I'm looking for... I need to do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

=IF(AND(A1="red",A2="blue"),10,IF(AND(A1="green",A 2="orange"),20,IF(AND(A1="purple",A2="yellow"),30, 0)))

You said you need to replicate this down column of 5 rows? That makes me
think that maybe your A1 and A2 references might really be A1 and B1?? But
in any case, the formula above should get you going. That last zero in the
formula could even be changed to a phrase like ,"Not a correct pairing")))


"Shelly" wrote:

Hello - I've searched, but cannot find what I'm looking for... I need to do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

ooops!

I missed a couple of quotes:

=IF(AND(A1="red",A2="blue"),10,IF(AND(A1="green",A 2="orange"),20,IF(AND(A1="purple",A2="yellow"),30, 0)))

Biff

"T. Valko" wrote in message
...
One way:

=IF(AND(A1="red",A2="blue"),10,IF(AND(A1="green",A 2="orange"),20,IF(AND(A1=purple",A2=yellow"),30,0) ))

You didn't say what you want if *none* of those conditions are met so the
formula will return 0.

Biff

"Shelly" wrote in message
...
Hello - I've searched, but cannot find what I'm looking for... I need to
do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

Judging by the other inputs, you get top points for Orginality and are well
up the scale on Execution. Very good!

"Gary''s Student" wrote:

=(A1="red")*(A2="blue")*10+(A1="green")*(A2="orang e")*20+(A1="purple")*(A2="yellow")*30


no IFs are needed.
--
Gary's Student
gsnu200706


"Shelly" wrote:

Hello - I've searched, but cannot find what I'm looking for... I need to do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

Try something like this:

With
A1 and A2 containing color names

A3: =SUM((A1&A2={"RedBlue","GreenOrange","PurpleYellow "})*{10,20,30})

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Shelly" wrote:

Hello - I've searched, but cannot find what I'm looking for... I need to do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

Both versions average calculation time is exactly the same*: 0.00023 secs

* using Charles Williams' RangeTimer method.

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Biff

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Judging by the other inputs, you get top points for Orginality and are
well
up the scale on Execution. Very good!

"Gary''s Student" wrote:

=(A1="red")*(A2="blue")*10+(A1="green")*(A2="orang e")*20+(A1="purple")*(A2="yellow")*30


no IFs are needed.
--
Gary's Student
gsnu200706


"Shelly" wrote:

Hello - I've searched, but cannot find what I'm looking for... I need
to do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

Hello,

I suggest to introduce a character which does not appear, or you could
get a wrong hit if a1=RedB and b1=lue:

=VLOOKUP(A1&"|"&B1,{"Red|Blue",10;"Green|Orange",2 0;"Purple|Yellow",
30},2,)

Regards,
Bernd

I understand what you're suggesting, but the formula you posted would return
an error if the test values don't match any of the pairs. For example, in
your posted scenario, the formula returns #NA.

Also, I considered a separator character and a few other modifications to
the posted formula, but decided against them for a few reasons:

1)I suspect the values of "red", "green", etc are only posted to give us
something easy to work with. The actual values may be different.

2)I would hope there would be control over the content of the cells (data
validation, import from a database, etc), obviating the need for error traps.

3)Without ALL of the details about possible cell content, we could create
some huge formulas that attempt to trap possibilities that would never occur.

Also, if somebody could enter "RedB" in one cell and "lue" in the next, why
not "Red|" and "Blue"?

Let's see if we get more specifics about the data.

***********
Regards,
Ron

XL2002, WinXP


"Bernd" wrote:

Hello,

I suggest to introduce a character which does not appear, or you could
get a wrong hit if a1=RedB and b1=lue:

=VLOOKUP(A1&"|"&B1,{"Red|Blue",10;"Green|Orange",2 0;"Purple|Yellow",
30},2,)

Regards,
Bernd

Hi Ron,

My formula just drafts the idea.
We can easily put the Red|Blue combinations into 2 columns, attach a
=A1 and =B1 at the end to ensure that the search combination will be
found and that it will return any "else" value we define.

Regards,
Bernd

PS: Red| and Blue cannot be separated from Red and |Blue. I mentioned
that the introduced character should not appear [in the input, of
course] ...

I think all the suggestion are good.
I am extremely lazy and my suggestion requires only 87 keystrokes.
--
Gary's Student
gsnu200706


"JLatham" wrote:

Judging by the other inputs, you get top points for Orginality and are well
up the scale on Execution. Very good!

"Gary''s Student" wrote:

=(A1="red")*(A2="blue")*10+(A1="green")*(A2="orang e")*20+(A1="purple")*(A2="yellow")*30


no IFs are needed.
--
Gary's Student
gsnu200706


"Shelly" wrote:

Hello - I've searched, but cannot find what I'm looking for... I need to do
this:

In the cell A3 the formula should say

If A1 = "red" and if A2 = "blue" enter 10 in A3, or
If A1 = "green" and if A2 = "orange" enter 20 in A3, or
If A1 = "purple" and if A2 = "yellow" enter 30 in A3.

And, I will need to replicate this down a column of 5 rows.

THANKS!!

Thanks everyone for your responses... let me clarify a few things. I was
wrong in listing my cells. They should be:

If A1 = "red" and if B1 = "blue" enter 10 in C1, or
If A1 = "green" and if B1 = "orange" enter 20 in C1, or
If A1 = "purple" and if B1 = "yellow" enter 30 in C1.

If no conditions apply, C1 can be left blank.

You are correct that the data given here is not the real data... just
placeholders. Don't know if the actual data will help, but here's a
description:

In a reference shet, I have two columns and 5 rows, with 5 combinations of
computer types - a database server in the first column, and an application
server in the 2nd column. I need to assign pricing to these combinations.
The selections the user will enter will always be in the pairs as they are
listed in the columns/rows (row1 column1 and row 1 column 2 will always be
paired, row 2 and column 1 and row 2 and column 2 will always be paried, etc).

I am using these lists of hardware in other places, so I need to keep them
in 2 columns - not combine the selections in one cell.

When a combination is picked (by using names and data validation), I need to
insert a price in the open cell (C1).

I'm going to try some of the options above and see if they work.

Thanks!

Try something like this:

With a sheet named: RefSheet
A1:C6 contains this list (Row_1 holds the col headings)

DB_Server AppServer Price
Red Blue 10
Green Orange 20
Purple Yellow 30
Brown Teal 40
Taupe Ecru 50

Then....on another sheet
A1: (a DB Server Name.....eg Green)
B1: (an App Server Name...eg Orange)

Then this formula returns the price for that combination:
C1:
=SUMPRODUCT((A1&B1=RefSheet!$A$2:$A$6&RefSheet!$B$ 2:$B$6)*RefSheet!$C$2:$C$6)

In the above example, the formula returns: 20

Note: There are other efficiencies to be gained by named ranges, if that
formula does what you want.

Is that something you can work with?

Post back with more questions.

***********
Regards,
Ron

XL2002, WinXP


"Shelly" wrote:

Thanks everyone for your responses... let me clarify a few things. I was
wrong in listing my cells. They should be:

If A1 = "red" and if B1 = "blue" enter 10 in C1, or
If A1 = "green" and if B1 = "orange" enter 20 in C1, or
If A1 = "purple" and if B1 = "yellow" enter 30 in C1.

If no conditions apply, C1 can be left blank.

You are correct that the data given here is not the real data... just
placeholders. Don't know if the actual data will help, but here's a
description:

In a reference shet, I have two columns and 5 rows, with 5 combinations of
computer types - a database server in the first column, and an application
server in the 2nd column. I need to assign pricing to these combinations.
The selections the user will enter will always be in the pairs as they are
listed in the columns/rows (row1 column1 and row 1 column 2 will always be
paired, row 2 and column 1 and row 2 and column 2 will always be paried, etc).

I am using these lists of hardware in other places, so I need to keep them
in 2 columns - not combine the selections in one cell.

When a combination is picked (by using names and data validation), I need to
insert a price in the open cell (C1).

I'm going to try some of the options above and see if they work.

Thanks!

Thanks Ron - I was actually able to get it work by using

=IF(AND(A1="red",A2="blue"),10,IF(AND(A1="green",A 2="orange"),20,IF(AND(A2="purple",A2="yellow"),30, "")))

But now I have another question...

On the reference Sheet, again, looking at the 2 columns DB_Server and
Apps_Server, with the 5 rows, making 5 possible combinations.

On the worksheet where the user is working, I have data validation for the
first cell set to the named range of DB_Server. When a user selects the
first DB Server in the list (RefSheet:A1), I want to return the App Server
listed first (RefSheet:B1), into the cell in which the user is working
(DataSheet!A1)

If the user selects the 2nd DB server in the list (RefSheet:A2), I want the
2nd App Server returned (RefSheet:B2) into DataSheet!A1.

and so forth...

THANK YOU!

GOT IT!

Found this in another post:

=IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$300,0)),"",INDEX( Sheet1!$B$1:$B$300,MATCH(A1,Sheet1!$A$1:$A$300,0)) )