How to apply VLOOKUP without entering a column number

How to apply VLOOKUP without entering a column number

How to apply VLOOKUP withoout enterning a column number

Are you still using VLOOKUP in the same old fashion and wonder how to apply VLOOKUP without entering a column number. Here is the solution

You’re existing VLOOKUP formula looks like this

=VLOOKUP(#cellreference,#data_range,#column number,0)

Try this new VLOOKUP formula

= VLOOKUP(#cell_reference,#data_range, MATCH(#cell_reference,#data_range,exact_match(0)),0)

Steps to apply the new VLOOKUP formula

Assuming you have 2 sheets, sheet1 has a data table and sheet 2 where you need to apply the new VLOOKUP formula

Sheet1 – Data Table

Sheet 1 - Data Table
Sheet 1 – Data Table

Sheet 2 – Where you need to apply the new VLOOKUP formula

Sheet2
Sheet2 -where you need to apply the new VLOOKUP formula

Step – 1 – Go to sheet 2 and copy paste the normal VLOOKUP formula in cell “C2”

=VLOOKUP($B2,Sheet1!$B$2:$G$11,0)

Step – 2 – After entering  the formula in cell “C2” just edit the formula as mentioned below, hit “F2” to edit the cell

=VLOOKUP($B2,Sheet1!$B$1:$G$11,MATCH(C$1,Sheet1!$B$1:$G$1,0),0)

Sheet 2 – New VLOOKUP formula applied

Vlookup with Match Formula
Sheet2 – new VLOOKUP formula applied

Step – 3 – Copy the formula to the used range. Left to Right for all columns and Top to Bottom for all rows. You don’t need to change anything in the formula. You can use the drag option to copy a formula (a + sign at the right bottom of the cell).

Take care of following while applying the formula

  • “$” sign is necessary to freeze the cell reference
  • Column Heading in each data set should be matched exactly else “Match” formula will not work properly
  • “Match Formula” is being used to identify the column number
  • Type “0” for an exact match in the Match formula

COMMENTS