Excel: guide with VLOOKUP returning N/A when searching for exact matches with text values
I'm confused about I'm stuck on something that should probably be simple..... I'm working with a frustrating scenario with the VLOOKUP function in Excel 2019. I have two sheets: 'Data' and 'Lookup'. In the 'Data' sheet, column A contains product IDs as text values, while in the 'Lookup' sheet, I have the same product IDs that I want to use to retrieve their corresponding prices from the 'Data' sheet in column B. Here's the formula I am using in the 'Lookup' sheet: ```excel =VLOOKUP(A2, Data!A:B, 2, FALSE) ``` However, the formula is consistently returning N/A for several IDs that I can visually confirm exist in the 'Data' sheet. I've already checked for leading/trailing spaces with the TRIM function and ensured that both columns are formatted as 'Text'. I also tried using the following approach to troubleshoot: ```excel =IFERROR(VLOOKUP(A2, Data!A:B, 2, FALSE), "Not Found") ``` This still returns 'Not Found' for some entries that are supposed to match. Additionally, I verified that there are no hidden characters in the cells by using the CODE function to check their ASCII values. Both sheets do not have any filters applied, and I’ve even tried copying the values from 'Data' to a new temporary sheet just to rule out any formatting issues. Despite all of this, I still face the same scenario. Is there something I'm missing or an edge case that VLOOKUP might be running into? Any insights would be appreciated! I'm working on a application that needs to handle this. I'm coming from a different tech stack and learning Excel Formula. Any ideas how to fix this? I'm working in a Ubuntu 22.04 environment.