if cell d5 says a then d6 will show 1 etc
 

how can i get a multiple answers from one cell to another.

Example my output cell will be d6
if cell d5 says hello then d6 will say 100
if cell d5 says help then d6 will say 300
if cell d5 says test then d6 will say 500
if cell d5 says bye then d6 will say 700
etc..


i got one but its only for two answers, i need at least 20
=IF(A1="Pass",100,IF(A1="Fail",0,""))

Please help !!!!!!!:confused: :confused:


--
Moh
------------------------------------------------------------------------
Moh's Profile: http://www.excelforum.com/member.php...o&userid=35434
View this thread: http://www.excelforum.com/showthread...hreadid=552060

if cell d5 says a then d6 will show 1 etc
 

Moh
Have a look at creating a VLOOKUP table
HTH
Michael M


"Moh" wrote:


how can i get a multiple answers from one cell to another.

Example my output cell will be d6
if cell d5 says hello then d6 will say 100
if cell d5 says help then d6 will say 300
if cell d5 says test then d6 will say 500
if cell d5 says bye then d6 will say 700
etc..


i got one but its only for two answers, i need at least 20
=IF(A1="Pass",100,IF(A1="Fail",0,""))

Please help !!!!!!!:confused: :confused:


--
Moh
------------------------------------------------------------------------
Moh's Profile: http://www.excelforum.com/member.php...o&userid=35434
View this thread: http://www.excelforum.com/showthread...hreadid=552060

if cell d5 says a then d6 will show 1 etc
 

Set up an array somewhere, say F1 to G4:

F G
Hello 100
Help 300
Test 500
Bye 700

Then do the following Formula at D6

=VLOOKUP(D5,F1:G4,2)

How you can expend your conditions without nesting a HUGE amount of IFs


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=552060

if cell d5 says a then d6 will show 1 etc
 

One way is to use VLOOKUP
(with the 4th param set to zero for exact match)

First, set up a reference table array
in say: Sheet1's cols A and B, eg:

hello 100
help 300
test 500
bye 700
etc

Then in any other sheet, we could use:
in D6: =VLOOKUP(D5,Sheet1!$A:$B,2,0)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moh" wrote:
how can i get a multiple answers from one cell to another.

Example my output cell will be d6
if cell d5 says hello then d6 will say 100
if cell d5 says help then d6 will say 300
if cell d5 says test then d6 will say 500
if cell d5 says bye then d6 will say 700
etc..


i got one but its only for two answers, i need at least 20
=IF(A1="Pass",100,IF(A1="Fail",0,""))

Please help !!!!!!!:confused: :confused:


--
Moh
------------------------------------------------------------------------
Moh's Profile: http://www.excelforum.com/member.php...o&userid=35434
View this thread: http://www.excelforum.com/showthread...hreadid=552060

if cell d5 says a then d6 will show 1 etc
 

in D6: =VLOOKUP(D5,Sheet1!$A:$B,2,0)

Perhaps better with an error trap:
in D6: =IF(D5="","",VLOOKUP(D5,Sheet1!$A:$B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

if cell d5 says a then d6 will show 1 etc
 

You might also wish to include all your choices within the formula itself:

In D6 enter:

=LOOKUP(D5,{"bye","hello","help","test";700,100,30 0,500})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Moh" wrote in message
...

how can i get a multiple answers from one cell to another.

Example my output cell will be d6
if cell d5 says hello then d6 will say 100
if cell d5 says help then d6 will say 300
if cell d5 says test then d6 will say 500
if cell d5 says bye then d6 will say 700
etc..


i got one but its only for two answers, i need at least 20
=IF(A1="Pass",100,IF(A1="Fail",0,""))

Please help !!!!!!!:confused: :confused:


--
Moh
------------------------------------------------------------------------
Moh's Profile:
http://www.excelforum.com/member.php...o&userid=35434
View this thread: http://www.excelforum.com/showthread...hreadid=552060

if cell d5 says a then d6 will show 1 etc
 

this is something im looking for. the thing is all the product are going to
be in 1 cell in a drop down list (d5) then in d6 whatever is select from d5
will give me an answer.

This is what i tried with your help but it returns with #N/A
=LOOKUP(D5,{"Fusion","Openzone","Featureline 1 Year","Voip";15,35,5,78})
--
please can you help... its urgent


"Ragdyer" wrote:

You might also wish to include all your choices within the formula itself:

In D6 enter:

=LOOKUP(D5,{"bye","hello","help","test";700,100,30 0,500})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Moh" wrote in message
...

how can i get a multiple answers from one cell to another.

Example my output cell will be d6
if cell d5 says hello then d6 will say 100
if cell d5 says help then d6 will say 300
if cell d5 says test then d6 will say 500
if cell d5 says bye then d6 will say 700
etc..


i got one but its only for two answers, i need at least 20
=IF(A1="Pass",100,IF(A1="Fail",0,""))

Please help !!!!!!!:confused: :confused:


--
Moh
------------------------------------------------------------------------
Moh's Profile:
http://www.excelforum.com/member.php...o&userid=35434
View this thread: http://www.excelforum.com/showthread...hreadid=552060

if cell d5 says a then d6 will show 1 etc
 

"Moh" wrote:
This is what i tried ... but it returns with #N/A
=LOOKUP(D5,{"Fusion","Openzone","Featureline 1 Year","Voip";15,35,5,78})

Think the lookup_vector** needs to be sorted in ascending order "A-Z",
viz. try it in D6 as:
=LOOKUP(D5,{"Featureline 1 Year","Fusion","Openzone","Voip";5,15,35,78})

**the part: {"Fusion","Openzone","Featureline 1 Year","Voip"; ...

But perhaps a less ambiguous way is to use vlookup with 4th param set to
zero/FALSE for an exact match (as suggested earlier).

Here's an adaptation which suits your context ..

In D6:
=IF(D5="","",VLOOKUP(D5,{"Fusion",15;"Openzone",35 ;"Featureline 1
Year",5;"Voip",78},2,0))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---