Author: Oscar Cronquist Article last updated on August 26, 2019 Show
 This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: I m working on the below table. ORDER MODEL MATERIAL QTY STATUS BOM a s6 1 COMPLETED BOM b c6 2 NOT COMPLETED BOM c s6 1 COMPLETED DEL d c6 3 NOT COMPLETED EXP a a8 4 IN PROGRESS DEL b d2 5 COMPLETED DEL c c6 4 NOT COMPLETED DEL d s6 7 NOT COMPLETED DEL e c6 8 NOT COMPLETED DEL r a8 1 COMPLETED EXP g d1 5 COMPLETED EXP r c6 9 COMPLETED EXP t a8 2 COMPLETED EXP a c6 1 NOT COMPLETED EXP b s6 9 COMPLETED EXP c c6 1 NOT COMPLETED EXP d a8 4 NOT COMPLETED I need the status column to be vlooked up on another file by comparing all the remaining 4 columns.(the sheet to be updated carries the 4 columns not in the same order as in the original sheet.. its mixed).. Thanks Sheet2 - Criteria and resultThe following array formula uses the corresponding values in column A, B, C and D to do a lookup in Sheet1 and return a value in column E. Array formula in cell E2, sheet2: =INDEX(Sheet1!$E$2:$E$18, MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!$D$2:$D$18, Sheet2!B2, Sheet1!$B$2:$B$18, Sheet2!C2, Sheet1!$C$2:$C$18, Sheet2!D2, Sheet1!$A$2:$A$18), 0)) Recommended article: Recommended articles The following article demonstrates how to do a lookup and return a sorted list: Recommended articles How to create an array formula
Recommended articles
Explaining array formula in cell E2I recommend that you use the "Evaluate Formula" feature in Excel to troubleshoot or to simply understand how a formula works. Select the cell containing the formula you want to evaluate, go to tab "Formulas" on the ribbon. Press with mouse on "Evaluate Formula" button to start evaluating. A dialog box appears, press with left mouse button on the "Evaluate" button to go through the formula calculations step by step. Step 1 - Count the number of cells by a given set of criteriaThe COUNTIFS function can work with up to 127 argument pairs: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) There are four criteria and the COUNTIFS function requires eight arguments, in order to get an array of values that we can use the second argument in each pair is a cell range. You are probably not used to this setup but it works fine, the array allows you to identify where the match is or in other words where all criteria match. COUNTIFS(Sheet2!A2, Sheet1!$D$2:$D$18, Sheet2!B2, Sheet1!$B$2:$B$18, Sheet2!C2, Sheet1!$C$2:$C$18, Sheet2!D2, Sheet1!$A$2:$A$18) returns the following array {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}. This array tells us that the match is in row 3 because 1 is in the third position in the array. Note that all criteria must match in order to return 1. Step 2 - Return the relative position of an item in an array that matches a specified valueThe MATCH function returns the relative position of a value in a cell range or array. MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!$D$2:$D$18, Sheet2!B2, Sheet1!$B$2:$B$18, Sheet2!C2, Sheet1!$C$2:$C$18, Sheet2!D2, Sheet1!$A$2:$A$18), 0) becomes MATCH(1, {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, 0) and returns 3. Value 1 is found in the third position in the array. Step 3 - Return the value of a cell at the intersection of a particular row and columnThe INDEX function returns a value based on a row and column number, there is only a row number in this case so you can omit the column argument. INDEX(cell_reference, [row_num], [column_num]) The first argument is the cell reference from which you want to get a specific value from. INDEX(Sheet1!$E$2:$E$18, MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!$D$2:$D$18, Sheet2!B2, Sheet1!$B$2:$B$18, Sheet2!C2, Sheet1!$C$2:$C$18, Sheet2!D2, Sheet1!$A$2:$A$18), 0)) becomes INDEX(Sheet1!$E$2:$E$18, 3) and returns "COMPLETED" in cell E2. Author: Oscar Cronquist Article last updated on May 24, 2022 I will in this article demonstrate several techniques that extract or filter records based on two conditions applied to a single column in your dataset. For example, if you use the array formula then the result will refresh instantly when you enter new start and end values. The remaining built-in techniques need a little more manual work in order to apply new conditions, however, they are fast. The downside with the array formula is that it may become slow if you are working with huge amounts of data. I have also written an article in case you need to find records that match one condition in one column and another condition in another column. The following article shows you how to build a formula that uses an arbitrary number of conditions: Extract records where all criteria match if not empty This article Extract records between two dates is very similar to the current one you are reading right now, Excel dates are actually numbers formatted as dates in Excel. If you want to search for a text string within a given date range then read this article: Filter records based on a date range and a text string I must recommend this article if you want to do a wildcard search across all columns in a data set, it also returns all matching records. If you want to extract records based on criteria and not a numerical range then read this part of this article. 1. Extract all rows from a range based on range criteria [Array formula]The picture above shows you a dataset in cell range B3:E12, the search parameters are in D14:D16. The search results are in B20:E22. Update 20 Sep 2017, a smaller formula in cell A20. Array formula in cell A20: =INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)> =$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1)) Back to top 1.1 VideoSee this video to learn more about the formula: Back to top 1.2 How to enter this array formula
The formula bar now shows the formula with a beginning and ending curly bracket, that is if you did the above steps correctly. Like this: {=array_formula} Don't enter these characters yourself, they appear automatically. Now copy cell A20 and paste to cell range A20:E22. Back to top 1.3 Explaining array formula in cell A20You can follow along if you select cell A19, go to tab "Formulas" on the ribbon and press with left mouse button on the "Evaluate Formula" button. Step 1 - Filter a specific column in cell range B3:E12The INDEX function is mostly used for getting a single value from a given cell range, however, it can also return an entire column or row from a cell range. This is exactly what I am doing here, the column number specified in cell D16 determines which column to extract. INDEX($B$3:$E$12, , $D$16, 1) becomes INDEX($B$3:$E$12, , 3, 1) and returns C3:C12. Recommended articles Step 2 - Check which values are smaller or equal to the conditionThe smaller than and equal sign are logical operators that let you compare value to value, in this case, if a number is smaller than or equal to another number. The output is a boolean value, True och False. Their positions in the array correspond to the positions in the cell range. INDEX($B$3:$E$12, , $D$16, 1)< =$D$15 becomes C3:C12< =$D$15 becomes {2; 6; 4; 5; 3; 9; 3; 2; 0; 1}<=6 and returns {TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE}. Step 3 - Multiply arrays - AND logicThere is a second condition we need to evaluate before we know which records are in range. (INDEX($B$3:$E$12, , $D$16, 1)< =$D$15)*(INDEX($B$3:$E$12, , $D$16, 1)> =$D$14) becomes ({2; 6; 4; 5; 3; 9; 3; 2; 0; 1}< =$C$14)*({2; 6; 4; 5; 3; 9; 3; 2; 0; 1}> =$C$13) becomes ({2; 6; 4; 5; 3; 9; 3; 2; 0; 1}< =3)*({2; 6; 4; 5; 3; 9; 3; 2; 0; 1}> =0) becomes {TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE} Both conditions must be met, the asterisk lets us multiple the arrays meaning AND logic. TRUE * TRUE equals FALSE, all other combinations return False. TRUE * FALSE equals FALSE and so on. {TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE} * {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE} returns {1; 0; 0; 0; 1; 0; 1; 1; 1; 1}. Boolean values have numerical equivalents, TRUE = 1 and FALSE equals 0 (zero). They are converted when you perform an arithmetic operation in a formula. Step 4 - Create number sequenceThe ROW function calculates the row number of a cell reference. ROW(reference) ROW($B$3:$E$12) returns {3; 4; 5; 6; 7; 8; 9; 10; 11; 12}. Step 5 - Create a number sequence from 1 to nThe MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order. MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)) becomes MATCH({3; 4; 5; 6; 7; 8; 9; 10; 11; 12}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12}) and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}. Step 6 - Return the corresponding row numberThe IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE. IF(logical_test, [value_if_true], [value_if_false]) IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)> =$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "") becomes IF({1; 0; 0; 0; 1; 0; 1; 1; 1; 1}, MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "") becomes IF({1; 0; 0; 0; 1; 0; 1; 1; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, "") and returns {1; ""; ""; ""; 5; ""; 7; 8; 9; 10}. Step 7 - Extract k-th smallest row numberThe SMALL function returns the k-th smallest value from a group of numbers. SMALL(array, k) SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)> =$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)) becomes SMALL({1; ""; ""; ""; 5; ""; 7; 8; 9; 10}, ROWS(B20:$B$20)) becomes SMALL({1; ""; ""; ""; 5; ""; 7; 8; 9; 10}, 1) and returns 1. Step 8 - Return the entire row record from the cell rangeThe INDEX function returns a value from a cell range, you specify which value based on a row and column number. INDEX(array, [row_num], [column_num]) INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)> =$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1)) becomes INDEX($B$3:$E$12, 1, , 1) and returns {2, "Ken Smith", 6, "North"}. Back to top Recommended articles Back to top 2. Extract all rows from a range based on range criteria - Excel 365Update 17 December 2020, the new FILTER function is now available for Excel 365 users. Formula in cell B20: =FILTER($B$3:$E$12, (D3:D12<=D15)*(D3:D12>=D14)) It is a regular formula, however, it returns an array of values and extends automatically to cells below and to the right. Microsoft calls this a dynamic array and spilled array. The array formula below is for earlier Excel versions, it searches for values that meet a range criterion (cell D14 and D15), the formula lets you change the column to search in with cell D16. This formula can be used with whatever dataset size and shape. To search the first column, type 1 in cell D16. Back to top 2.1 Explaining array formulaStep 1 - First conditionThe less than character and the equal sign are both logical operators meaning they are able to compare value to value, the output is a boolean value. In this case, the logical expression evaluates if numbers in D3:D12 are smaller than or equal to the condition specified in cell D15. D3:D12<=D15 becomes {2;6;4;5;3;9;3;2;0;1}<=6 and returns {TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE}. Step 2 - Second conditionThe second condition checks if the number in D3:D12 are larger than or equal to the condition specified in cell D14. D3:D12>=D14 becomes {2;6;4;5;3;9;3;2;0;1}>=4 and returns {FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}. Step 3 - Multiply arrays - AND logicThe asterisk lets you multiply a number to a number, in this case, array to array. Both arrays must be of the exact same size. The parentheses let you control the order of operation, we want to evaluate the comparisons first before we multiply the arrays. (D3:D12<=D15)*(D3:D12>=D14) becomes {TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE}*{FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE} and returns {0; 1; 1; 1; 0; 0; 0; 0; 0; 0}. AND logic works like this: TRUE * TRUE = TRUE (1) TRUE * FALSE = FALSE (0) FALSE * TRUE = FALSE (0) FALSE * FALSE = FALSE (0) Note that multiplying boolean values returns their numerical equivalents. Step 4 - Filter values based on arrayThe FILTER function lets you extract values/rows based on a condition or criteria. FILTER($B$3:$E$12, (D3:D12<=D15)*(D3:D12>=D14)) becomes FILTER({1, "John Doe", 2, "North"; 2, "Ken Smith", 6, "North"; 3, "Abraham Johnson", 4, "South"; 4, "Don Williams", 5, "West"; 5, "Brenda Jones", 3, "South"; 6, "Kenneth Brown", 9, "East"; 7, "Jennifer Davis", 3, "West"; 8, "Brittany Miller", 2, "West"; 9, "Martin Wilson", 0, "South"; 10, "Roger Moore", 1, "East"}, (D3:D12<=D15)*(D3:D12>=D14)) becomes FILTER({1, "John Doe", 2, "North"; 2, "Ken Smith", 6, "North"; 3, "Abraham Johnson", 4, "South"; 4, "Don Williams", 5, "West"; 5, "Brenda Jones", 3, "South"; 6, "Kenneth Brown", 9, "East"; 7, "Jennifer Davis", 3, "West"; 8, "Brittany Miller", 2, "West"; 9, "Martin Wilson", 0, "South"; 10, "Roger Moore", 1, "East"}, {0; 1; 1; 1; 0; 0; 0; 0; 0; 0}) and returns {2, "Ken Smith", 6, "North"; 3, "Abraham Johnson", 4, "South"; 4, "Don Williams", 5, "West"}. Back to top 3. Extract all rows from a range that meet the criteria in one column [Array formula]The array formula in cell B20 extracts records where column E equals either "South" or "East". The following array formula in cell B20 is for earlier Excel versions than Excel 365: =INDEX($B$3:$E$12, SMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($B$2:B2)) To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys. The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself. Back to top 3.1 Explaining formula in cell B20Step 1 - Filter a specific column in cell range $A$2:$D$11The COUNTIF function allows you to identify cells in range $E$3:$E$12 that equals $E$15:$E$16. COUNTIF($E$15:$E$16,$E$3:$E$12) becomes COUNTIF({"South"; "East"},{"North"; "North"; "South"; "West"; "South"; "East"; "West"; "West"; "South"; "East"}) and returns {0;0;1;0;1;1;0;0;1;1}. Step 2 - Return corresponding row numberThe IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3). The logical expression was calculated in step 1 , TRUE equals 1 and FALSE equals 0 (zero). IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "") becomes IF({0; 0; 1; 0; 1; 1; 0; 0; 1; 1}, MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "") becomes IF({0; 0; 1; 0; 1; 1; 0; 0; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, "") and returns {""; ""; 3; ""; 5; 6; ""; ""; 9; 10}. Step 3 - Find k-th smallest row numberSMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)) becomes SMALL({""; ""; 3; ""; 5; 6; ""; ""; 9; 10}, ROWS(B20:$B$20)) becomes SMALL({""; ""; 3; ""; 5; 6; ""; ""; 9; 10}, 1) and returns 3. Step 4 - Return value based on row and column numberThe INDEX function returns a value based on a cell reference and column/row numbers. INDEX($B$3:$E$12, SMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($B$2:B2)) becomes INDEX($B$3:$E$12, 3, COLUMNS($B$2:B2)) becomes INDEX($B$3:$E$12, 3, 1) and returns 3 in cell B20. Back to top Recommended articles Back to top 4. Extract all rows from a range based on multiple conditions - Excel 365Update 17 December 2020, the new FILTER function is now available for Excel 365 users. Formula in cell B20: =FILTER($B$3:$E$12, COUNTIF(E15:E16, E3:E12)) It is a regular formula, however, it returns an array of values. Read here how it works: Filter values based on criteria The formula extends automatically to cells below and to the right. Microsoft calls this a dynamic array and spilled array. Back to top 4.1 Explaining array formulaStep 1 - Check if values equal criteriaThe COUNTIF function calculates the number of cells that meet a given condition. COUNTIF($E$15:$E$16,$E$3:$E$12) becomes COUNTIF({"South"; "East"},{"North"; "North"; "South"; "West"; "South"; "East"; "West"; "West"; "South"; "East"}) and returns {0; 0; 1; 0; 1; 1; 0; 0; 1; 1}. Step 2 - Filter records based on arrayThe FILTER function lets you extract values/rows based on a condition or criteria. FILTER($B$3:$E$12, COUNTIF(E15:E16, E3:E12)) becomes FILTER($B$3:$E$12, {0; 0; 1; 0; 1; 1; 0; 0; 1; 1}) and returns {3, "Abraham Johnson", 4, "South"; 5, "Brenda Jones", 3, "South"; 6, "Kenneth Brown", 9, "East"; 9, "Martin Wilson", 0, "South"; 10, "Roger Moore", 1, "East"}. Back to top 5. Extract all rows from a range that meet the criteria in one column [Excel defined Table]The image above shows a dataset converted to an Excel defined Table, a number filter has been applied to the third column in the table. Here are the instructions to create an Excel Table and filter values in column 3.
The image above shows the Excel defined Table, here is how to filter D between 4 and 6:
Back to top 6. Extract all rows from a range that meet the criteria in one column [Filter]The image above shows filtered records based on two conditions, values in column D are larger or equal to 4 or smaller or equal to 6. Here is how to apply Filter arrows to a dataset.
Black arrows appear next to each header. Lets filter records based on conditions applied to column D.
Back to top 7. Extract all rows from a range that meet the criteria in one column [Advanced Filter]The image above shows a filtered dataset in cell range B5:E15 using Advanced Filter which is a powerful feature in Excel. Here is how to apply a filter:
Back to top Recommended articles
Extract duplicate records
Filter unique distinct records Back to top 8. Excel fileBack to top |
A lookup table should contain at least two rows and two columns, not counting headings.
Pos Terkait
Copyright © 2024 ketiadaan Inc.
