Value depends on comination of 2 columns
 

Hi all,

I'm trying to work out the easiest way for the following:

I have a drop-down list with 5 text values in column K
I have a drop-down list with 3 text values in column L
Based on the comination of the selection in column K and column L I
want Excel to provide a pre-defined value in column L.

Example:
If column K=option1 and column L=optionA then column M should be 20
If column K=option2 and column L=optionA then column M should be 50
If column K=option1 and column L=optionC then column M should be 10

Is there a formula / function that will accomplish this?
Thanks in advance for your help.

Sp00k


--
Sp00k
------------------------------------------------------------------------
Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676
View this thread: http://www.excelforum.com/showthread...hreadid=534469

Value depends on comination of 2 columns
 

Hey Sp00k,

To provide maximum future proofing I would be inclined to do the following
rather than try to put it all in one big IF or CHOOSE combination.

For this example I'll assume the following:
K1 has the drop down list with a selection of A B C D E
L1 has the drop down list with a selection of X Y Z

I would build a hidden result list say columns N and O so your possible
results a
Col N Col O
A-X 10
A-Y 12
A-Z 14 etc etc to cover all combinations

Then the formula in M1 would be:
=vlookup($K$1&"-"&$L$1,$N:$O,2,0)

This joins the results of K1 and L1 together with a seperating dash and then
brings back the value from your results table.

Doing it this way means you can grow your options list without having to
mess with the formula.

HTH

Giz

"Sp00k" wrote:


Hi all,

I'm trying to work out the easiest way for the following:

I have a drop-down list with 5 text values in column K
I have a drop-down list with 3 text values in column L
Based on the comination of the selection in column K and column L I
want Excel to provide a pre-defined value in column L.

Example:
If column K=option1 and column L=optionA then column M should be 20
If column K=option2 and column L=optionA then column M should be 50
If column K=option1 and column L=optionC then column M should be 10

Is there a formula / function that will accomplish this?
Thanks in advance for your help.

Sp00k


--
Sp00k
------------------------------------------------------------------------
Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676
View this thread: http://www.excelforum.com/showthread...hreadid=534469

Value depends on comination of 2 columns
 

Build a 5x3 lookup table with optionsf or col L as column headers, and
options for col M as row headers.
Fill your lookup values as desired (eg: 20 in col "option1" row "optionA")


With your table in A1:F4,
Enter following formula in column N:
=INDEX($B$2:$F$4,MATCH(K1,$B$1:$F$1,0),MATCH(L1,$A $2:$A$4,0))

HTH
--
AP


"Sp00k" a écrit dans le
message de ...

Hi all,

I'm trying to work out the easiest way for the following:

I have a drop-down list with 5 text values in column K
I have a drop-down list with 3 text values in column L
Based on the comination of the selection in column K and column L I
want Excel to provide a pre-defined value in column L.

Example:
If column K=option1 and column L=optionA then column M should be 20
If column K=option2 and column L=optionA then column M should be 50
If column K=option1 and column L=optionC then column M should be 10

Is there a formula / function that will accomplish this?
Thanks in advance for your help.

Sp00k


--
Sp00k
------------------------------------------------------------------------
Sp00k's Profile:
http://www.excelforum.com/member.php...o&userid=33676
View this thread: http://www.excelforum.com/showthread...hreadid=534469

Value depends on comination of 2 columns
 

Thanks for your reply Giz. I've tried your suggestion and it works
great!

The only thing now is that when no values are provided in both column K
and L, the value returned is #N/A. Is there any way that the returned
value is 0 when nothing is entered in columns K and L?

Sp00k


--
Sp00k
------------------------------------------------------------------------
Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676
View this thread: http://www.excelforum.com/showthread...hreadid=534469

Value depends on comination of 2 columns
 

Thanks for your suggestion Ardus. I am going to need a bit of time to
analyze your formula to put the correct column / row values in (sorry,
newbie when it comes to Excel formulas:) ). I'll let you know the
results when I've worked it out.

Sp00k


--
Sp00k
------------------------------------------------------------------------
Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676
View this thread: http://www.excelforum.com/showthread...hreadid=534469

Value depends on comination of 2 columns
 

=if(isna(vlookup($K$1&"-"&$L$1,$N:$O,2,0)),0,vlookup($K$1&"-"&$L$1,$N:$O,2,0))

If it's invalid (i.e. not in your table of combinations) you get #N/A, this
revised formula tests for a #N/A error and returns 0 (zero) if true,
otherwise it performs the lookup.

HTH

Giz

"Sp00k" wrote:


Thanks for your reply Giz. I've tried your suggestion and it works
great!

The only thing now is that when no values are provided in both column K
and L, the value returned is #N/A. Is there any way that the returned
value is 0 when nothing is entered in columns K and L?

Sp00k


--
Sp00k
------------------------------------------------------------------------
Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676
View this thread: http://www.excelforum.com/showthread...hreadid=534469

Value depends on comination of 2 columns
 

This works great! Thanks for your help Giz!

Sp00k


--
Sp00k
------------------------------------------------------------------------
Sp00k's Profile: http://www.excelforum.com/member.php...o&userid=33676
View this thread: http://www.excelforum.com/showthread...hreadid=534469