The VLOOKUP() function finds a value in the first column of a range and returns the corresponding value from another column. The return column number can be hard-coded in the third argument to VLOOKUP, like in this example which returns the value in column 3
I have a health insurance spreadsheet where I need to do a VLOOKUP to get an employee’s health insurance deduction for a particular month. Every month is represented in the spreadsheet, and I don’t want to have to write 12 different formulas.
To get a return column based on another cell, I use a MATCH function for the column argument. Here’s an example:
I can use the column headings in this table as part of my MATCH formula to get the proper month. If I have an input cell for the name and one for the month, the formula would look like this:
Because my match range B1:D1 has one less column than the lookup table, I add one to the result of the MATCH. MATCH returns the position of the matching value, in this case 1 as Month1 is the first value in B1:D1. Of course I’d never want the column number in my VLOOKUP to be 1, because that would just return the name.