VLOOKUP function is a practical function to lookup 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.
3 Possible Reasons Why Excel VLOOKUP Is Returning Column Header Instead of Value
Excel 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 function, RIGHT function, MID function, or TRIM function 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 would get the #N/A error in the result.
Now, to solve this issue, you can follow the steps below.
- 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.
You can use this tool to convert the data format into the Number format or Date as per your requirements and the error issue would 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: [Fixed!] Excel VLOOKUP Returning #N/A Error (6 Possible Solutions)
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.
- 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.
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.
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.
Read More: [Fixed!] VLOOKUP Not Working Between Sheets (8 Easy Fixes)
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.
And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!