VLOOKUP is one of the most widely used Excel functions for retrieving data from large tables. When combined with the COLUMNS or MATCH functions, it becomes more powerful and dynamic, allowing formulas to automatically adjust when copied or when the table structure changes.
VLOOKUP Function
VLOOKUP is used to search for a value in the first column of a table and return a corresponding value from another column.
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Example
We assume a large dataset where quickly retrieving book details like price and author is required without using manual search (CTRL+F). The table contains Book Title, Author, and Price columns.
The book title is selected using a Data Validation List (drop-down), and in columns C and D, VLOOKUP is used to retrieve the Author and Price separately. In cell C16, a standard VLOOKUP formula can be applied.
In cell C16, VLOOKUP retrieves the Author, while in cell D16 it retrieves the Price using a different column index.
However, manually changing the column index each time the formula is copied across columns is inefficient. Instead, the COLUMNS function can be used to dynamically generate the column index, making the formula faster and easier to scale. In cell C16, the formula becomes:
=VLOOKUP(G4,B1:D11,COLUMNS($B4:B4)+1,0)
Note: Make sure you utilize the COLUMNS (plural) not COLUMN, as they work in an unexpected way.
COLUMNS Function
The COLUMNS capability returns the quantity of sections in a cluster. The language structure is =COLUMNS(array), where 'cluster' is the section range. For instance:
=COLUMNS($B4:B4)
It gives us 1. For example, the exhibit is 1 section wide. So all we're truly doing is working out the segment number we need utilizing the COLUMNS capability.
Note: $B4:B4 could simply been plain B:B like this =COLUMNS($B:B), as it doesn't exactly make any difference what the column reference is.
Using VLOOKUP with COLUMNS Function
In our VLOOKUP equation above (in cell C16), our COLUMNS recipe is COLUMNS($B4:B4)+1, because,
- The segment we need returned is the second section in our VLOOKUP table,
- What's more, since =COLUMNS($B4:B4) assesses to 1 we really want to add 1 to get 2 for the subsequent segment.
In the VLOOKUP function in cell D16 our COLUMNS capability is COLUMNS($B4:C4)+1 and it assesses to 2+1=3. We've utilized an outright cell reference on $B4:C4 (=2) so that when we replicated the VLOOKUP recipe opposite C16 to D16, the COLUMNS a piece of the equation will naturally increment by 1 (from $B4:B4 to $B4:C4) to give us the right segment number.
Two-Way Lookup with VLOOKUP and Match
Inside VLOOKUP, the column index is usually fixed as a static number. However, using the MATCH function allows you to dynamically determine the correct column position. This enables a two-way lookup by matching both rows and columns.
This makes the formula more flexible and reliable, as it continues to work even if columns are added or removed.
=VLOOKUP(G4,B1:D11,MATCH(G5,B1:D1,0),0)
Here, MATCH finds the column number based on the header, and VLOOKUP returns the corresponding value.
Explanation
This is a standard VLOOKUP formula where the column index is dynamically provided by the MATCH function. The range B1:D1 includes the header row (including B1 as a placeholder) to align MATCH output with the VLOOKUP table structure. MATCH returns the correct column position (e.g., 3 for Feb), which VLOOKUP then uses internally like this:
=VLOOKUP(G4,B1:D11,3,0)
This returns the value for the selected row and column (e.g., $41 for Feb sales).
Advantages
- Eliminates manual column updates
- Works efficiently in large datasets
- More flexible and scalable formulas
- Reduces human error in spreadsheets