Value depends on comination of 2 columns

[Sp00k]

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

Sp00

 

[Guest]

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 are:
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

 

[Ardus Petus]

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

 

[Sp00k]

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]

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

 

[Guest]

=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]

This works great! Thanks for your help Giz!

Sp00k