Why is my VLOOKUP returning erroneous data?

BMan@GGTI

I am using a cross-sheet VLOOKUP formula on several rows in a master sheet. It is working correctly in one column, but for the other column the formula is returning erroneous data for only a few rows. For instance, in a couple of cases it is finding a search value that does not exist in the referenced sheet, then returning an erroneous number. Why is this happening?

David Tutwiler

Without seeing the formula my best guess is that you are asking the formula to look at specific columns, but when you copy it across the columns the formula auto-updates and shifts your columns around. You can fix this by using the $ character to make your reference absolute.

For example: test4 would look at column test at row 4. when you copy it over to the next column it would change test to whatever the next column's name is. If you put $test$4 it would always reference test4, no matter where you copied the formula.

If this isn't your issue, could you paste the formula you have working and maybe a few screenshots?

BMan@GGTI

Hi, Adding the "$" either created errors or returned a "0."

So this is my formula: =IFERROR(VLOOKUP("GAOJA", {F&E Hours by Charge Code Range 5}, 18), 0). And the sheet below is the sheet for the referenced range. The last column to the far right, column 18 is a grand total of all the numbers in the row. There are no formulas in this sheet. It is a .csv import. But as you can see, there is no "GAOJA" in this sheet under Employee ID. For some reason the formula is returning "16."

David Tutwiler

You can try to add ,true) after your 18 specifying the column. This should force and exact match to have to be found.