=VLOOKUP() formulas typically force users to convert all their knowledge of a reference table into numbers. We know a table contains columns with clear headers such as ID, First Name, Last Name, Address, etc. And the =VLOOKUP function requires us to convert that column to a number (First Name on column B to column index number 2, Address on Column D to column index number 4, etc.).
If we had a data table spanning several columns, it would take while to track down the specific column index number required. There is a less strenuous process we can adopt, if we know the name of the column header we are looking for. This will involve nesting the =MATCH() function within the =VLOOKUP formula.
Instead of
=VLOOKUP(“Bat Roger”,$B$4:$Y$83,16,FALSE)
use
=VLOOKUP(“Bat Roger”,$B$4:$Y$83,MATCH(“Phone Number”,$B$4:$Y$4,0),FALSE)
[The formula above assumes column headers are on the first row – row 4, hence the reference to $B$4:$Y$4]
Beware: There is need to ensure the column header text perfectly matches the referenced text in the =VLOOKUP formula, otherwise this will return errors
