Excel VLOOKUP Returning Column Header Instead of Value

Reason 1 – The Dataset and Columns Are Not Correctly Organized

The first reason that contributes to returning column header instead of value is the unorganized dataset and columns.

Organize your dataset properly and in proper formats.
Select the lookup table as the first column should carry the lookup value.
When inputting the lookup table, don’t include the column headers in the table range.

Reason 2 – Wrong Match Type

There are two match types when working with the VLOOKUP function: TRUE is for Approximate Match and False is for Exact Match. When you put TRUE – Approximate Match then the VLOOKUP function can return the column header instead of the value.

Reason 3 – The Lookup Values Column Contains Unnecessary Letters

Use the LEFT, RIGHT, MID, or TRIM functions according to unnecessary letters or characters in different positions to extract exact lookup values column data.

4 More Issues We Face While Using VLOOKUP Function in Excel

Issue 1 – Excel VLOOKUP Returns #N/A When the Value Exists

The VLOOKUP function returns specific values according to lookup values and the lookup table. But sometimes, it shows a #N/A error instead of values even if the value actually exists in the lookup table. This mainly happens for two reasons below.

Cell Format Mismatch:

If the lookup value and lookup table column values’ formats are not the same, then you will get the #N/A error in the result.

Solution:

When preparing the dataset at the very beginning, make sure the data are in the proper formats.
If the dataset is large to change again, then select your lookup value column data, go to the Data tab, select the Data Tools group, and choose the Text to Columns tool.

You can use this tool to convert the data format into the Number format or Date as per your requirements and the error issue will be solved.

Hidden Space / Characters:

Another reason for showing #N/A error is because of hidden space or characters inside the lookup value or lookup column values.

To get rid of this issue, incorporate the TRIM function into the lookup value cell reference and lookup table range reference.

Issue 2 – VLOOKUP Returning the Same Value

Solution:

Nake sure that the first column of the lookup table contains the lookup values.
Nake sure that the lookup table range is absolutely referenced. You can press the F4 key on your keyboard to do this.
Go to the Formula tab, select Calculation Options and choose Automatic.

Issue 3 – VLOOKUP Function Not Returning a Value but Formula

This mainly happens if you insert the formula in a Text-formatted cell.

Ensure that the cell is in the General or Number format before you insert the formula.

Issue 4 – VLOOKUP Not Working Between Sheets

The desired sheet name should be put before the lookup value cell reference or lookup table range reference inside the apostrophe (‘) on both sides and must have an exclamation sign (!) after the sheet name.
The lookup cell and the lookup table array have to be properly formatted and prepared.
The lookup table’s first column has to contain the lookup value and the match type should be False – Exact Match type.