Excel VLOOKUP Returning Column Header Instead of Value

Get FREE Advanced Excel Exercises with Solutions!

The VLOOKUP function is a practical function to look up specific values from a given dataset. When working with the VLOOKUP function, there are some sensitive things to keep in your head. Otherwise, you may get errors in the result or column header instead of values. In this article, I will discuss the reasons for returning the column header instead of value when working with the VLOOKUP function in Excel.


Why Excel VLOOKUP Is Returning Column Header Instead of Value: 3 Possible Reasons

The VLOOKUP function can return the column header instead of actual lookup values sometimes due to the 3 possible reasons.

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. You have to make sure that the following points are followed to avoid this reason.

  • First, you have to organize your dataset properly and in proper formats.
  • Second, you have to select the lookup table as the first column should carry the lookup value.
  • Third, when inputting the lookup table, it is better not to include the column headers in the table range.

Reason 2: Wrong Match Type

The second most common reason for returning the column header instead of a value is the wrong match type. There are two match types when working with the VLOOKUP function. One is, TRUE – Approximate Match and another is False – Exact Match. When you put TRUE – Approximate Match then the VLOOKUP function can return the column header instead of the value. So you must choose the False-Exact Match to get the actual result in this regard.


Reason 3: Lookup Values Column Containing Unnecessary Letters

Another important reason for showing the column header instead of the value is the lookup values column contains unnecessary letters.

You must 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

Now, the VLOOKUP function can have more issues other than the one stated above. Let’s look into them too!

1. Why Excel VLOOKUP Returns #N/A When 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.

Now, to solve this issue, you can follow the steps below.

📌 Steps:

  •  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 >> Data Tools group >> Text to Columns tool.

Access Text to Column Tool to Solve Excel VLOOKUP Returning #N/A Error Instead of Value

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.

Read More: Why VLOOKUP Returns #N/A When Match Exists


2. VLOOKUP Returning Same Value

Now, sometimes, it might happen that your VLOOKUP function is returning a value, but it’s returning the same value for all rows. In this case, you can follow the steps below to solve this.

📌 Steps:

  • First, make sure that the first column of the lookup table contains the lookup values.
  • Second, make sure that the lookup table range is absolutely referenced. You can press the F4 key on your keyboard to do this.
  • If the issue still exists, go to the Formula tab >> Calculation Options tool >> Automatic option.

Ensure that the Automatic Calculation is On for Formulas

Read More: [Fixed!]: VLOOKUP Function Is Returning Same Value in Excel


3. VLOOKUP Function Not Returning Value but Just Formula

Sometimes, it might happen that the cell is returning the inserted formula instead of the value. This mainly happens if you insert the formula in a Text formatted cell.

So, you must ensure that the cell is in the General or Number format before you insert the formula.

Read More: VLOOKUP Is Returning Just Formula Not Value in Excel


4. VLOOKUP Not Working Between Sheets

Sometimes, you might have to work with multiple sheets when working with the VLOOKUP function. But, you might get errors in this regard because of several reasons. To avoid this issue, you have to ensure the following things.

  • 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.

Conclusion

So, in this article, I have discussed possible reasons and solutions for the Excel VLOOKUP function returning column header instead of values. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.

Thank you!


Related Articles


<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo