Sunday, September 17, 2023

Google Sheets "#NA" Vlookup Error Fix

One of the most important advantages of Google Sheets is that you can use the same form/sheet for routine (daily/weekly/monthly) reports by just updating the raw data for the new report. However, sometimes when we update the sheet with new data, we get an error and instead of the expected result we get the "#NA" error on the cell. This is because, unseen by us, these data might not be just the literal value of the text but also carries metadata (text style, font-size, etc). And when vlookup tries to look for the value in the new data, they would not find the exact match. Work-around to this error is, select-all the new data, go to title bar, go to format/number, select plain text. Then go back to title bar, go to format/number, select automatic. You have just erased all metadata from the raw data and "#NA" error should be fixed.