How to Vlookup using Match in Excel?

You can use the MATCH function with VLOOKUP to search for values in Excel. Here's how to do it:

  1. Make sure your data is organized in a table with column headers.
  2. Determine which column you want to search for the value (this will be the "lookup column").
  3. In a cell where you want to display the result of the VLOOKUP function, type the formula "=VLOOKUP(lookup_value, table_array, MATCH(lookup_column, table_array_header_row, 0), range_lookup)".
  4. Replace "lookup_value" with the value you want to search for, "table_array" with the range of cells that contains your data (including column headers), "lookup_column" with the column you want to search in, "table_array_header_row" with the row that contains the column headers, and "range_lookup" with either TRUE or FALSE depending on whether you want an approximate or exact match.

Here's an example: Let's say you have a table that lists the names of employees and their salaries. You want to find the salary of the employee with ID number "1234". The employee ID numbers are in column A, and the salaries are in column B.

  1. First, find the position of the "Salary" column by using MATCH: =MATCH("Salary", A1:B1, 0)

    This will return the number 2, since the "Salary" column is the second column in the table.

  2. Use VLOOKUP with the MATCH function to look up the employee's salary: =VLOOKUP(1234, A1:B5, MATCH("Salary", A1:B1, 0), FALSE)

    In this formula, "1234" is the lookup_value, A1:B5 is the table_array, MATCH("Salary", A1:B1, 0) returns the column index number (2), and FALSE specifies that an exact match is required.

When you press Enter, the formula will return the salary of the employee with ID number "1234".