Contents
Introduction
Excel is a powerful tool for managing and analyzing data. Two of
its most useful functions for performing lookups and retrieving specific
information from a dataset are INDEX and MATCH. This article will provide a
detailed guide on how to use these functions with practical examples. We’ll
break down each formula, explain how it works, and demonstrate its application
in real-world scenarios.
Syntax
INDEX Function
The INDEX function returns the value of a cell located at a
specific row and column within a given range. Its syntax is:
=INDEX(array, row_num, [column_num])
- array: The range of cells containing
the data.
- row_num: The row number in the array
from which to retrieve the value.
- [column_num]:
(Optional) The column number in the array from which to retrieve the
value.
MATCH Function
The MATCH function searches for a specified value in a range and
returns its position. Its syntax is:
=MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: The
value you want to find.
- lookup_array: The
range of cells to search within.
- [match_type]:
(Optional) Specifies the match type; 0 for an exact match.
When combined, INDEX and MATCH can perform powerful lookups by
first finding the position of the desired data with MATCH and then using that
position to retrieve the corresponding value with INDEX.
Practical Field Uses
These functions are especially useful for tasks such as:
- Retrieving data from large tables.
- Combining multiple criteria for lookups.
- Finding values based on dynamic or
changing conditions.
10 Practical Examples
1. Find the Price of
"Laptop" from Dell
To find the price of a
"Laptop" from Dell, you can use the following formula:
=INDEX(E2:E11,
MATCH("Laptop", B2:B11, 0))
In this formula, MATCH("Laptop",
B2:B11, 0) searches for the word "Laptop" in the range B2:B11 (which
contains the equipment names). The 0 indicates that we want an exact match. The
MATCH function returns the position of "Laptop" within that range.
Next, the INDEX(E2:E11, ...) function uses this position to look up the
corresponding price from the price column, E2:E11. This formula efficiently
retrieves the price for the specified laptop.
2. Find the Supplier of the
"Router"
To find the supplier of a
"Router," you can use the formula:
=INDEX(H2:H11,
MATCH("Router", B2:B11, 0))
Similar to the previous example,
this formula uses MATCH to locate the "Router" in the equipment names
(B2:B11). Once the position is found, the INDEX function looks up the
supplier's name from the range H2:H11, where the supplier data is stored. This
allows you to quickly identify which supplier provides the Router.
3. Find the Supplier of
Equipment Costing $200
To retrieve the supplier for
equipment priced at $200, you can use this formula:
=INDEX(H2:H11, MATCH(200, E2:E11,
0))
Here, the MATCH function looks
for the value 200 in the price column, E2:E11. Once it finds the corresponding
position, the INDEX function retrieves the supplier's name from the range H2:H11.
This formula is useful for pinpointing which supplier offers equipment at a
specific price.
4. Find the Equipment with the
Lowest Price
To find the equipment that has
the lowest price, use the following formula:
=INDEX(B2:B11, MATCH(MIN(E2:E11),
E2:E11, 0))
In this case, MIN(E2:E11)
determines the lowest price in the price column. The MATCH function then
locates this lowest price’s position in the same column. Finally, the INDEX
function retrieves the corresponding equipment name from the B2:B11 range. This
allows you to easily find the equipment that is the most affordable.
5. Find the Warranty Period
for Equipment with the Highest Rating
To find out the warranty period
for the equipment with the highest rating, use this formula:
=INDEX(I2:I11, MATCH(MAX(J2:J11),
J2:J11, 0))
This formula first uses MAX(J2:J11)
to identify the highest rating among the ratings in column J. The MATCH
function then finds the position of this highest rating. Finally, INDEX(I2:I11,
...) returns the corresponding warranty period from the I2:I11 range, which
contains warranty information. This makes it easy to see the warranty for
top-rated equipment.
6. Find the Equipment with
Stock Greater Than 50
To identify equipment that has a
stock greater than 50, you can use the following formula:
=INDEX(B2:B11, MATCH(TRUE,
F2:F11>50, 0))
In this formula, F2:F11>50
creates an array of TRUE and FALSE values, indicating whether each item’s stock
exceeds 50. The MATCH(TRUE, ..., 0) finds the first instance of TRUE in this
array, meaning it will locate the first piece of equipment that meets the stock
criteria. The INDEX function then retrieves the name of that equipment from the
B2:B11 range.
7. Find the Price of the
"Keyboard" Using Both Category and Name
To find the price of a
"Keyboard" specifically within the "Peripherals" category,
use the following formula:
=INDEX(E2:E11, MATCH(1,
(C2:C11="Peripherals")*(B2:B11="Keyboard"), 0))
This formula employs an array
calculation. (C2:C11="Peripherals")*(B2:B11="Keyboard") generates
an array where both conditions (category and name) must be met simultaneously.
The MATCH function looks for 1 in this array, indicating that both conditions
were satisfied. Then, INDEX retrieves the price from the E2:E11 range based on
the matching position.
8. Find the Location of the
"Laptop" Supplied by "TechCorp"
To find the location of a
"Laptop" provided by "TechCorp," use this formula:
=INDEX(G2:G11, MATCH(1,
(B2:B11="Laptop")*(H2:H11="TechCorp"), 0))
In this case, the array formula
checks both conditions: whether the item is a "Laptop" and whether
the supplier is "TechCorp." The MATCH function identifies the
position where both conditions are true, and INDEX then retrieves the
corresponding location from the G2:G11 range. This helps in quickly identifying
where the specified laptop is located.
9. Find the Supplier of the
Highest-Rated Equipment Dynamically
To dynamically find the supplier
of the highest-rated equipment, use the following formula:
=INDEX(H2:H11, MATCH(MAX(J2:J11),
J2:J11, 0))
This formula is similar to the
one used to find the warranty period for the highest-rated equipment. MAX(J2:J11)
identifies the highest rating, and MATCH finds its position in the rating
column. Finally, INDEX retrieves the corresponding supplier from the H2:H11
range, making it easy to dynamically fetch supplier information based on
ratings.
10. Find the Supplier for
Equipment with Price Lower Than $200
To find suppliers for equipment
priced lower than $200, you can use this formula:
=INDEX(H2:H11, MATCH(TRUE,
E2:E11<200, 0))
Similar to the earlier example
about stock, this formula checks the price condition. E2:E11<200 creates an
array of TRUE and FALSE values based on whether each price is less than $200.
The MATCH function finds the first TRUE, and INDEX retrieves the corresponding
supplier’s name from H2:H11. This allows for easy identification of suppliers
for lower-priced equipment.
Conclusion
Understanding and using the INDEX and MATCH functions in Excel can
significantly enhance your ability to retrieve specific data from large
datasets. By mastering these formulas, you can efficiently perform lookups,
handle multiple criteria, and dynamically extract relevant information to meet
your needs.
Click here Template File Download
No comments:
Post a Comment