In this article we will learn how to retrieve an item from a list that meets multiple criteria, we use Index and Match function in Microsoft Excel.
MATCH: The Match formula returns the cell number where the value is found in a horizontal or vertical range.
Syntax of “MATCH” function:=MATCH(lookup_value,lookup_array,[match_type])
There is 3 match type 1st is 1-less than, 2nd is 0-Exact match, 3rd is (-1)-greater than. For example:- We have data in range A1: C5. Column A contains Order Id, column B contains Unit Price, and column C contains Quantity. Follow below given steps:-
INDEX: The Index formula returns a value from the intersection between the row number and the column number in an Array. There are 2 syntaxes for the “INDEX” function.
1stSyntaxof“INDEX”function: =INDEX (array, row_num, [column_num])
2nd Syntax of “INDEX” function:=INDEX (reference, row_num, [column_num], [area_num])
For example: - We have data in range A1:C5. Column A contains Order Id, column B contains Unit Price, and column C contains Quantity. Follow below given steps:-
Let’s take an example to understand how to retrieve an item from a list that meets multiple criteria. We have 2 data sets. Range 1 (A2:C7) contains file names with their matching types and sizes. Range 2 (A10:B15) contains a similar list of file names but with matching types only. Now, we want to find the appropriate file sizes from Range1 by matching each pair of file names and types from both the ranges. Follow below given steps:-
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.