How to list conditional lists in Excel
The Lookup function is available in Excel so that users can search by conditions but return only the first true value. If you need to list all the conditions and copy to another data sheet or another sheet . then there is no direct tool that satisfies this requirement.
Consider the following example: You summarize data table and need to extract all data conditionally into another table.
So how can this be done? Today, the Software Dexterity will guide you how to combine the available functions to list all the conditional values in Excel as functions: IFERROR, INDEX, SMALL .
IFERROR function
Function structure: = IFERROR (value, value_if_error) .
Inside:
- IFERROR : is the name of the function used to detect the error value and return the value you need.
- Value : The value we need to determine is an error or not.
- Value_if_error : return value if value is error.
If the value of the value is an error (# N / A, # REF, .), the IFERROR function will return the value_if_error , otherwise it will return a value value .
INDEX function
Function syntax: = INDEX (array, row_num, [column_num]) .
Inside:
- INDEX : is the name of the function used to retrieve the value in row n ( row_num) in the array data array .
- Array : Data array. In this article, the Software Tips only guide INDEX function with array as an array of data containing only one column.
- row_num : Select the row in the array from which to return a value.
Example of using the INDEX function:
SMALL function
Function syntax: = SMALL (array, k) .
Inside:
- SMALL : is the name of the function used to determine the smallest value k.
- Array: An array or range of numeric data that you want to determine its kth smallest value.
- K: Position (from the smallest value) in an array or range of data to return.
Example of using the SMALL function:
How to list conditional lists in Excel
With the requirement that Dexterity Software made above, you need to list 3 revenue items of Hai Duong Branch in column J.
In column J4 enter the formula: = IFERROR (INDEX ($ G $ 1: $ G $ 12, SMALL (IF ($ J $ 2 = $ B $ 3: $ B $ 12, ROW ($ G $ 3: $ G $ 12)), ROW (A1))), "") and press Ctrl + Shif + Enter . And copy the formula to other rows of columns.
Here:
- $ G $ 1: $ G $ 12: is the reference data array to get the value to return.
- $ J $ 2 is the position of the cell containing the value to search.
- $ B $ 3: $ B $ 12 is the data array to be searched.
So based on the above formula, you change the position items of the data array and data cells corresponding to the position of your worksheet.
Note:
- You should leave the absolute value of the data array to avoid errors when copying formulas.
- If your reference range is long, you should copy the formula for the entire column or at least equal the number of rows of the reference range for safety. Ad has copied the formula until cell J9, but because only 3 search values are satisfied that the Hai Duong Branch condition should only contain cells J3-J4 with numeric data, the other cells will be blank data. and will not affect the aesthetics of the report.
The image below is another result if the Dexterity Software changes the reference condition: Ho Chi Minh Branch.
Good luck!
You should read it
- Instructions on how to create drop down lists or drop-down lists in Excel
- How to use Conditional Formatting to conditional formatting in Excel
- How to create a drop list in Excel 2016
- Ms Excel - Lesson 13: Use conditional formatting in Excel
- How to use conditional formatting in Microsoft Excel 2016
- How to create drop down lists in Excel
- Conditional counting function in Excel
- How to use DSUM function in Excel
May be interested
- How to use DSUM function in Excelthe dsum function on excel is used to calculate the conditional sum in a field, or column in the data list.
- How to create Progress bar using conditional formatting in Excel 2013, 2010 and 2007progress bar is very popular, it provides instant feedback on a specific process. so why not bring this handy progress bar to the spreadsheet using excel's conditional formatting feature.
- List in CSSthis article explains how to style the list in css.
- Steps to clear Jump Lists history on Windows 10jump lists are designed to provide users with quick access to documents and tasks related to the applications installed on the system. you can treat jump lists like a small start menu that includes specific applications
- How to use conditional formatting in Numbers on Macconditional formatting in spreadsheets is a great feature. if you want to use conditional formatting in numbers on a mac, read the following article.
- How to use conditional statistical functions in Excelhow to use conditional statistical functions in excel. statistical data is an important step to help you plan your business process. in the calculation process you cannot help but use the quick statistical functions in excel.
- DMAX () function (returns conditional maximum value) in Exceldmax () is the function that returns the maximum value in a column in a list or database that satisfies the condition that you give.
- How to format data based on other cell conditions in Excelconditional formatting in excel makes it easier to categorize or highlight data, based on the conditions you set.
- How to Create a List in Cells in Excelif you want to know how to create a list of multiple lines in a cell on microsoft excel, read this article! whether it's creating a bulleted list with line breaks, a numbered list, or a drop-down list, it's easy once you know where the options are. today's tipsmake will show you three useful ways to insert any kind of list into cells in excel.
- How to format conditional cells in Google Sheetson google sheets features conditional formatting conditional formatting cells. with this feature you can enter data with different values.