Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort the data in 8 columns by two of the columns? | Excel Worksheet Functions | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |