This is unparalleled for the level of flexibility in accomplishing data lookups. It’s much more superior to VLOOKUP and HLOOKUP lookup methods.
Exploring the MATCH Function
The MATCH function returns a relative position of a given value within a range. Its syntax is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
Lookup_value: The value to search for.
Lookup_array: The range to search from.
Match_type:0 for exact match,1 for less than, and -1 for greater than.
Example: =MATCH(42, A1:A10, 0) returns the row where 42 is located in the range A1:A10.
How INDEX Works?
The INDEX function returns a value at an addressed position in a range. Its syntax is:
=INDEX(array, row_num, [column_num])
Array: The range of cells.
Row_num: The row number of the desired value.
Column_num: Optional, specifies the column in a multi-column range.
Example: =INDEX(A1:C10, 2, 3) returns value in the second row and third column.
MATCH and INDEX
Their real power is when used together. In this regard:
=INDEX(B1:B10,MATCH(“John”, A1:A10, 0))
This finds “John” in Column A and returns value on Column B.
Using MATCH and INDEX
Dynamic Data Retrieval:
Quickly pull data based on changing criteria.
Multidirectional Search:
Unlike VLOOKUP which only looks horizontally or vertically but not up-down,
Conclusion
MATCH and INDEX is a good alternative to look up dynamic data in Excel. Their versatility makes them absolutely necessary for complicated spreadsheets.
Frequently Asked Questions
Why use MATCH and INDEX instead of VLOOKUP?
MATCH and INDEX can look any direction whereas VLOOKUP only looks left to right.
What happens if the value of MATCH is not found?
MATCH returns a #N/A error if it cannot find the value.
