if cell d5 says a then d6 will show 1 etc

  • Thread starter Thread starter Moh
  • Start date Start date
M

Moh

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:
 
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 IF
 
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)
 
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))

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

In D6 enter:

=LOOKUP(D5,{"bye","hello","help","test";700,100,300,500})
 
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 said:
You might also wish to include all your choices within the formula itself:

In D6 enter:

=LOOKUP(D5,{"bye","hello","help","test";700,100,300,500})
Click to expand...
 
Moh said:
This is what i tried ... but it returns with #N/A
=LOOKUP(D5,{"Fusion","Openzone","Featureline 1 Year","Voip";15,35,5,78})
Click to expand...

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))


---
 
Back
Top