Excel Formula for Lookup and Match

Formula Overview

To perform a lookup in a list and return a corresponding value, you can use the combination of the VLOOKUP or INDEX and MATCH functions in Excel. Below are explanations of both methods, followed by practical examples.

---

Method 1: Using VLOOKUP

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Parameters

• lookup_value: The value to search for in the first column of the table_array.
• table_array: The range of cells that contains the data.
• col_index_num: The column number in the table_array from which to retrieve the value.
• [range_lookup]: Optional; TRUE for an approximate match or FALSE for an exact match.

Example

Assuming you have a list of products in A2:A10 and their prices in B2:B10, you want to find the price of a specific product in cell D2.

=VLOOKUP(D2, A2:B10, 2, FALSE)

Explanation

• The function searches for the value in D2 within the range A2:A10.
• If it finds a match, it retrieves the corresponding price from column B.

---

Method 2: Using INDEX and MATCH

Syntax

INDEX(array, row_num, [column_num])
MATCH(lookup_value, lookup_array, [match_type])

Parameters

• INDEX:

  • array: The range from which to return a value.
  • row_num: The row number from which to return the value.
  • [column_num]: Optional; the column number from which to return the value.

• MATCH:

  • lookup_value: The value you want to find.
  • lookup_array: The range of cells to search.
  • [match_type]: Optional; 0 for an exact match.

Example

Using the same product and price lists, the formula to find the price in cell D2 would be:

=INDEX(B2:B10, MATCH(D2, A2:A10, 0))

Explanation

• The MATCH function finds the row number where the value in D2 appears in the range A2:A10.
• The INDEX function then retrieves the corresponding price from the range B2:B10 based on the row number returned by MATCH.

---

Practical Application

Scenario

Assume the following data:

• Column A (Products): Apple, Banana, Orange
• Column B (Prices): 1.00, 0.50, 0.75
• Cell D2: "Banana"

VLOOKUP Implementation

To find the price of "Banana":

=VLOOKUP(D2, A2:B4, 2, FALSE)

• Result: 0.50

INDEX and MATCH Implementation

To achieve the same result with INDEX and MATCH:

=INDEX(B2:B4, MATCH(D2, A2:A4, 0))

• Result: 0.50

---

Conclusion

Both VLOOKUP and the combination of INDEX and MATCH effectively perform lookups in Excel. Use VLOOKUP for a straightforward approach when working with left-to-right lookups, and utilize INDEX and MATCH for more flexibility, especially with right-to-left lookups.

To enhance your skills with Excel data functions, consider exploring courses on the Enterprise DNA Platform.