If you are looking for a formula which lookup & returns not just the first value meeting the criteria, but all of them, then this article is going to be helpful.
In this article, you will learn how to get multiple values from same criteria with different results.
We will use a combination of 6 functions i.e. INDEX, SMALL, IF, ROW, IFERROR,ROWS
INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
array: Array is a range of cells or table.
row_num: The row number in the array from which to return the value.
column_num: It is optional. It is the column number in the array which is used to return the value.
SMALL: Returns the k-th smallest value in a data set. For example: the fifth smallest number from a list of 20 items.
array: Itis an array or range of cells in a list of data for which you want to find the k-th smallest value.
k: It is the kth position from smallest value to return in the array or range of cells.
The IF function checks if a condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.
Syntax = IF(logical_test,value_if_true,value_if_false)
logical_test: Logical test will test the condition or criteria. If the condition meets then it returns the preset value, and if the condition does not meet then it returns another preset value.
value_if_true: The value that you want to be returned if this argument returns TRUE.
value_if_false: The value that you want to be returned if this argument returns FALSE
ROW: Returns the row number of a reference.
Reference: It is a reference to a cell or range of cells.
The IFERRORfunctionreturns value_if_error if expression is an error and the value of the expression itself otherwise
Rows: Returns the number of rows in a reference or array
array: Array is a reference to a range of cells or an array.
Let us understand with an example:
We have Product list in column A & Value in column B.
In cell D2, we have applied data validation for all the Products.
The formula should check the Product (cell D2) in column A, and if the product is found then it returns the value.
In cell E2, the formula would be =IFERROR(INDEX($B$2:$B$17,SMALL(IF($A$2:$A$17=$D$2,ROW($A$2:$A$17)-ROW($A$2)+1),ROWS($E$1:E1))),””)
This is an array formula which requires formula to be enclosed with curly brackets by using CTRL + SHIFT + ENTER.
By copying down the formula in column B, we will get the result
If we change the Product in cell D2, the result will automatically update. Refer below snapshot
In this way, you can find out multiple values from same criteria.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.