How to Dynamically Change the Column Reference in Excel Lookup Functions

In Excel, you can use lookup functions to search for a value in a range or array and return a corresponding value from another range or array. For example, you can use the VLOOKUP function to look up a value in the first column of a table and return a value from another column in the same row.

However, sometimes you may want to change the lookup column reference dynamically, depending on the criteria you specify. For example, you may want to look up a value in a different column based on the name of a doctor, as shown in the following table:

In this case, you cannot use a fixed column reference in the VLOOKUP function, such as VLOOKUP("01/01/2024",A1:D4,2,FALSE), because it will always return the value from the second column, which is Doctor A. You need to change the column reference into a function that can calculate the column number based on the doctor’s name.

Procedures

One way to change the lookup column reference into function in Excel formula is to use the COLUMN function. The COLUMN function returns the column number of a reference. For example, COLUMN(A1) returns 1, COLUMN(B1) returns 2, and so on.

To use the COLUMN function to change the lookup column reference, you need to do the following steps:

1. Create a cell that contains the doctor’s name you want to look up. For example, you can enter Doctor B in cell F1.
2. Use the COLUMN function to calculate the column number of the doctor’s name, based on the cell reference and the starting column of the table. For example, you can use the formula =COLUMN(F1)-COLUMN(A1)+1 in cell G1. This formula subtracts the column number of the starting column (A1) from the column number of the cell that contains the doctor’s name (F1), and adds 1 to get the relative column number. In this case, the formula returns 3, which is the column number of Doctor B in the table.
3. Use the VLOOKUP function to look up the value in the table, using the cell that contains the column number as the third argument. For example, you can use the formula =VLOOKUP("01/01/2024",A1:D4,G1,FALSE) in cell H1. This formula looks up the value in the first column of the table (A1:D4) that matches the date “01/01/2024”, and returns the value from the column number specified in cell G1, which is 3. In this case, the formula returns 15, which is the value of Doctor B on 01/01/2024.

Explanation

The following table shows the result of the above steps, along with the formulas used in each cell:

The advantage of this method is that you can change the doctor’s name in cell F1 and the lookup result in cell H1 will update automatically, without changing the formula. For example, if you change the doctor’s name to Doctor C, the column number will change to 4, and the lookup result will change to 20.

Example

To illustrate this method with a detailed example, let’s assume that you have a table that shows the sales of different products by different salespersons, as shown below:

You want to create a formula that can look up the sales of a specific product on a specific date, based on the name of the salesperson you enter in a cell. For example, you want to enter Bob in cell F1 and get the sales of his product on 02/01/2024 in cell H1.

To do this, you can use the following steps:

1. Enter Bob in cell F1.
2. Use the formula =COLUMN(F1)-COLUMN(A1)+1 in cell G1 to get the column number of Bob, which is 3.
3. Use the formula =VLOOKUP("02/01/2024",A1:E4,G1,FALSE) in cell H1 to get the sales of Bob’s product on 02/01/2024, which is 130.

The following table shows the result of the above steps, along with the formulas used in each cell:

Other Approaches

There are other ways to change the lookup column reference into function in Excel formula, such as using the MATCH function or the CHOOSE function. Here are some examples of how to use these functions:

• MATCH function: The MATCH function returns the relative position of a value in a range or array. For example, MATCH("Bob",B1:E1,0) returns 2, which is the position of Bob in the range B1:E1. You can use the MATCH function to replace the COLUMN function in the previous method, as follows:
  1. Enter Bob in cell F1.
  2. Use the formula =MATCH(F1,B1:E1,0) in cell G1 to get the column number of Bob, which is 2.
  3. Use the formula =VLOOKUP("02/01/2024",A1:E4,G1+1,FALSE) in cell H1 to get the sales of Bob’s product on 02/01/2024, which is 130. Note that you need to add 1 to the column number, because the VLOOKUP function starts counting from the first column of the table, which is A1.
• CHOOSE function: The CHOOSE function returns a value from a list of values, based on an index number. For example, CHOOSE(2,"Alice","Bob","Carol","David") returns Bob, which is the second value in the list. You can use the CHOOSE function to replace the VLOOKUP function in the previous method, as follows:
  1. Enter Bob in cell F1.
  2. Use the formula =MATCH(F1,B1:E1,0) in cell G1 to get the column number of Bob, which is 2.
  3. Use the formula =CHOOSE(G1,A2:A4,B2:B4,C2:C4,D2:D4) in cell H1 to get the sales of Bob’s product on 02/01/2024, which is 130. Note that you need to use the column number as the index number, and provide the ranges of each column as the values in the list.