Excel equivalent function

Question

I have an interesting task - I need to start checking the following data in Excel:

| A - B - C - D | |------|------|------|------| | 36 | 0 | 0 | x | | 0 | 600 | 700 | x | |___________________________| 

You must excuse my wonderfully bad ASCII art. Therefore, I need a D (x) column to perform a check against adjacent cells, and then, if necessary, convert the values. Here are the criteria:

If column B is greater than 0, everything works fine, and I can get coffee. If this does not meet this requirement, I need to convert A1 according to the table - for example, 32 = 1420 and put in D Unfortunately, there is no connection between A and what needs to be converted, so creating a calculation is out of the question.

A case or switch statement would be ideal in this scenario, but I don't think this is a native function in Excel. I also think it would be crazy to group a bunch of expressions =IF() , which I did about four times before deciding that this is a bad idea (my life story).

Answer 1

Sounds like work for VLOOKUP !

You can place 32 → 1420 type mappings in multiple columns somewhere, and then use the VLOOKUP function to perform a search.

Answer 2

Without reference to the original problem (which, I suspect, has long been resolved), I recently discovered a tricky trick that makes the selection function work just like the select case without the need to modify the data. There is only one catch: only one of the conditions you have chosen can be true at a time.

The syntax is as follows:

 CHOOSE( (1 * (CONDITION_1)) + (2 * (CONDITION_2)) + ... + (N * (CONDITION_N)), RESULT_1, RESULT_2, ... , RESULT_N ) 

Assuming that only one of the conditions from 1 to N is satisfied, everything else is 0, which means that the numerical value will correspond to the corresponding result.

If you are not 100% sure that all conditions are mutually exclusive, you may prefer something like:

 CHOOSE( (1 * TEST1) + (2 * TEST2) + (4 * TEST3) + (8 * TEST4) ... (2^N * TESTN) OUT1, OUT2, , OUT3, , , , OUT4 , , <LOTS OF COMMAS> , OUT5 ) 

However, if Excel has an upper limit on the number of arguments a function can take, you will reach it pretty quickly.

Honestly, I can’t believe that it took me years to figure this out, but I haven’t seen it before, so I decided that I would leave it here to help others.

EDIT: According to the comment below from @aTrusty: stupid numbers of commas can be excluded (and as a result, the select statement will work up to 254 cases) using the formula of the following form:

 CHOOSE( 1 + LOG(1 + (2*TEST1) + (4*TEST2) + (8*TEST3) + (16*TEST4),2), OTHERWISE, RESULT1, RESULT2, RESULT3, RESULT4 ) 

Notice the second argument in the LOG clause, which puts it in base 2 and makes it all work.

Edit: according to David's answer , there is now an actual expression about switching if you are fortunate enough to work in the office 2016. In addition to reading difficulties, this also means that you get switching efficiency, not just behavior!

Answer 3

Switch function is now available in Excel 2016 / Office 365

<sub> SWITCH (expression, value1, result1, [default or value2, result2], ... [default or value3, result3])

 example: =SWITCH(A1,0,"FALSE",-1,"TRUE","Maybe") 

sub>

Microsoft Support

Answer 4

Try it;

 =IF(B1>=0, B1, OFFSET($X$1, MATCH(B1, $X:$X, Z) - 1, Y) 

WHERE X = columns that you index in
Y = number of columns left (-Y) or right (Y) indexed column to get the value you are looking for
Z = 0 if exact match (if you want to handle errors)

Answer 5

I used this solution to convert one-letter color codes to their descriptions:

 =CHOOSE(FIND(H5,"GYR"),"Good","OK","Bad") 

You basically look for the element you are trying to decode in the array, then use CHOOSE() to select the associated element. This is a bit more compact than creating a table for VLOOKUP() .

Answer 6

I know it's a little late to answer, but I think this short video will help you a lot.

http://www.xlninja.com/2012/07/25/excel-choose-function-explained/

In fact, it is a function of choice. He explains it very well in the video, so I will do it instead of typing 20 pages.

Another video explains how to use data validation to populate a drop-down list that you can select from a limited range.

http://www.xlninja.com/2012/08/13/excel-data-validation-using-dependent-lists/

You can combine the two and use the value in the drop-down list, as your index, for the select function. Until he showed how to combine them, I'm sure you can understand how his videos are good. If you have a problem, let me know and I will update my answer to show you.

Answer 7

I understand that this is an answer to an old post

I like the If () function combined with Index () / Match ():

 =IF(B2>0,"x",INDEX($H$2:$I$9,MATCH(A2,$H$2:$H$9,0),2)) 

The if function compares what is in column b, and if it is greater than 0, it returns x if it does not use the array (information table) identified by the Index () function and selected by Match () to return the value corresponding to a.

The index array has an absolute location set by $H$2:$I$9 (dollar signs), so the place it points to does not change as the formula is copied. The string with the value you want to return is identified by the Match () function. Match () has an added value that does not need a sorted list to see what Vlookup () requires. Match () can find a value with a value: 1 less, 0 exact, -1 more. I put a zero after the absolute array Match () $H$2:$H$9 to find the exact match. For the column, the value of the Index () array will be entered, which will be returned. I entered 2 because in my array the return value was in the second column. Below my index array looked like this:

 32 1420 36 1650 40 1790 44 1860 55 2010 

The value in column “a” for searching the list is in the first column in my example, and the corresponding value, which should be returned, is on the right. The lookup / link table can be on any tab in the workbook - or even in another file. -Book2 is the name of the file, and Sheet2 is the name of another tab.

 =IF(B2>0,"x",INDEX([Book2]Sheet2!$A$1:$B$8,MATCH(A2,[Book2]Sheet2!$A$1:$A$8,0),2)) 

If you do not want x to return when b is greater than zero, remove x for the "empty" / zero equivalent, or possibly put 0 - not sure what you would like there.

The following is the beginning of a function with x removed.

 =IF(B2>0,"",INDEX... 

Answer 8

Even if the old one seems to be popular questions, so I will post another solution, which I think is very elegant:

http://fiveminutelessons.com/learn-microsoft-excel/using-multiple-if-statements-excel

This is elegant because it uses only the IF function. Basically, it boils down to the following:

if (condition, select / use a value from the table, if (condition, select / use another value from the table ...

And so on

Works great, even better than HLOOKUP or VLOOOKUP

but ... Be warned - there is a limit to the number of nested if statements that excel can handle.

Answer 9

If you do not have a SWITCH statement in your version of Excel (prior to Excel-2016), here is the VBA implementation for it:

 Public Function SWITCH(ParamArray args() As Variant) As Variant Dim i As Integer Dim val As Variant Dim tmp As Variant If ((UBound(args) - LBound(args)) = 0) Or (((UBound(args) - LBound(args)) Mod 2 = 0)) Then Error 450 'Invalid arguments Else val = args(LBound(args)) i = LBound(args) + 1 tmp = args(UBound(args)) While (i < UBound(args)) If val = args(i) Then tmp = args(i + 1) End If i = i + 2 Wend End If SWITCH = tmp End Function 

It works exactly as expected, a replacement, for example, for the SWITCH function of Google Spreadsheet.

Syntax:

 =SWITCH(selector; [keyN; valueN;] ... defaultvalue) 

Where

• a selector is any expression that compares with keys
• key1, key2, ... - expressions that are compared with the selector
• value1, value2, ... are the values ​​that are selected if the selector is equal to the corresponding key (only)
• the default value is used if no key matches the selector

Examples:

 =SWITCH("a";"?") returns "?" =SWITCH("a";"a";"1";"?") returns "1" =SWITCH("x";"a";"1";"?") returns "?" =SWITCH("b";"a";"1";"b";TRUE;"?") returns TRUE =SWITCH(7;7;1;7;2;0) returns 2 =SWITCH("a";"a";"1") returns #VALUE! 

To use it, open Excel, go to the Development Tools tab, click Visual Basic, right-click ThisWorkbook, select Paste, then Module, and finally copy the code to the editor. You must save the Excel workbook (xlsm) as a macro environment.

Answer 10

Microsoft replaces SWITCH, IFS, and IFVALUES with the CHOOSE function only.

 =CHOOSE($L$1,"index_1","Index_2","Index_3")