i am not sure how to phrase this, but this is what i am after...

I want to populate cell B1 with a particular value based on multiple
conditions in cell A1. I need cell B1 to do the following:

- If cell A1 = "Dog" then the value of B1 should equal the value in cell E21
- If cell A1 = "Cat" then the value of B1 should equal the value in cell F21
- If cell A1 = "Mouse" then the value of B1 should equal the value in cell G21

how the heck do i do this? is this even possible?

many thanks,
doon

Try this in B1:

=IF(A1="Dog",E21,IF(A1="Cat",F21,IF(A1="Mouse",G21 ,"Not specified")))

The phrase "Not specified" is returned if none of the criteria are met.
There is a limit of 7 to the number of IF statements you can "nest" in
this way - if you have more criteria then there are other ways of
achieving what you require.

Hope this helps.

Pete

Try this formula in B1

=INDEX(E21:G21,MATCH(A1,{"Dog","Cat","Mouse"},0))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=511528

hi pete,

i tried this, but get an error. excel highlights this part of the formula:
""Dog",E21,IF"

any thoughts?
doon

hi there,

tried this suggestion as well. excel seems to have an issue with this part
of the formula: "G21,MATCH"

any thoughts?
d

Hi, doon

both my suggestion and Pete's work for me :)

are you using those exact formulas?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=511528

got it to work!

i used pete's first. i had to change all of the commas to semicolons and
then it all worked.

my looks like this:
=IF(B5=B31;C32;IF(B5=E31;F32;IF(B5=H31;I32;"Not specified")))

i am referencing other cells to give me the dog, cat, mouse terms in my
example. somehow, the semicolons seem strange, but this was the magic fix.

thanks again to you both!
doon

p.s. just about to try the second suggestion and will try applying the same
"fix" there as well...

It's just that your regional settings are different than those of the people
who supplied you answers. I use commas, as I believe do most people who are
in the United States, but for some reason, apparently, different countries or
regions use the semicolon for delimiter. I don't remember ever reading that
some regions use other than those two.
--
Kevin Vaughn


"doon" wrote:

got it to work!

i used pete's first. i had to change all of the commas to semicolons and
then it all worked.

my looks like this:
=IF(B5=B31;C32;IF(B5=E31;F32;IF(B5=H31;I32;"Not specified")))

i am referencing other cells to give me the dog, cat, mouse terms in my
example. somehow, the semicolons seem strange, but this was the magic fix.

thanks again to you both!
doon

p.s. just about to try the second suggestion and will try applying the same
"fix" there as well...