Utilize VLOOKUP function for two-way lookup

Buzz

In addition to the MATCH function, you can also use the VLOOKUP function to create more flexible two-way lookups. In this article, Mytour will guide you on how to use the VLOOKUP function for two-way lookup.

Basically, the VLOOKUP function may break when columns are inserted or deleted from the table, but formulas containing VLOOKUP + MATCH functions can still operate accurately even when columns change. So, you can use the VLOOKUP function for two-way lookup easily and accurately.

Utilize VLOOKUP function for two-way lookup

Universal Formula

=VLOOKUP(lookup_value,table,MATCH(col_name,col_headers,0),0)

Explanation

In the VLOOKUP function, the column index argument is usually hardcoded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create dynamic two-way lookups, combining both rows and columns.

Furthermore, you can utilize the VLOOKUP function for more flexible two-way lookups: VLOOKUP may break when columns are inserted or deleted from the table, but formulas containing VLOOKUP + MATCH functions can still operate accurately even when columns change.

Example

In the example below, a formula containing VLOOKUP + MATCH functions is used to automatically search through rows and columns:

=VLOOKUP(H2,B3:E11,MATCH(H3,B2:E2,0),0)

H2 provides values for rows, while H3 provides lookup values for columns.

How does this formula work?

This is the standard VLOOKUP formula, except for one thing: the column index is provided by the MATCH function.

Note that the lookup array provided to the MATCH function (B2:E2) represents the column headers, including the empty cell B2. This is done to synchronize the value returned by the MATCH function with the table used by the VLOOKUP function.

In other words, you must provide the MATCH function with a data range having the same number of columns as the VLOOKUP function is using in the table. In the example (for month 2), the MATCH function returns the value 3, so after the MATCH function runs, the VLOOKUP formula will look like this:

=VLOOKUP(H2,B3:E11,3,0)

Returns the revenue for Colby (4th row) in month 2 (column number 3), which is $6,786.

Above is how to use the VLOOKUP function for bidirectional lookup. If you encounter any errors such as #N/A error when using VLOOKUP function and have any questions or concerns, especially #N/A errors, you can leave your comments below. Remember to follow Mytour's upcoming articles to learn more about information, how to use VLOOKUP function!

The content is developed by the Mytour team with the aim of customer care and solely to inspire travel experiences. We do not take responsibility for or provide advice for other purposes.

If you find this article inappropriate or containing errors, please contact us via email at [email protected]

Frequently Asked Questions

How can the VLOOKUP function be used for two-way lookups effectively?

The VLOOKUP function can be effectively used for two-way lookups by combining it with the MATCH function. This allows you to create dynamic column indices, ensuring that your lookup remains accurate even when columns are added or removed.

What are the advantages of using VLOOKUP with the MATCH function?

Using VLOOKUP with the MATCH function provides flexibility, allowing your formulas to adapt to changes in the table structure. This combination helps avoid errors when columns are inserted or deleted, ensuring accurate lookups.

Can the VLOOKUP function break when columns are deleted from the table?

Yes, the VLOOKUP function can break if columns are deleted. However, using VLOOKUP combined with the MATCH function allows your lookup to remain functional and accurate despite changes in the table.