Excel VLOOKUP returning incorrect values when using named ranges with merged cells
I'm migrating some code and Hey everyone, I'm running into an issue that's driving me crazy... Can someone help me understand I'm relatively new to this, so bear with me. I've been banging my head against this for hours. I'm working with an unexpected scenario with VLOOKUP in Excel when trying to retrieve data from a table where I have merged cells. The named range is defined to include those merged cells, but when I run the VLOOKUP, it returns incorrect values. For example, I'm using the following formula: ```excel =VLOOKUP(A2, DataRange, 2, FALSE) ``` In this case, `DataRange` is a named range that includes merged cells in the first column. When A2 contains a value that should match a merged cell, I get the value from the next row instead of the correct corresponding value. I've tried adjusting the named range to cover only unmerged cells, but that doesn't seem to resolve the scenario. Additionally, I've ensured that my lookup values are formatted the same way as the values in the named range. I also attempted to use INDEX and MATCH combinations: ```excel =INDEX(DataRange, MATCH(A2, INDEX(DataRange, 0, 1), 0), 2) ``` However, this still yields inaccurate results. I’ve checked for leading/trailing spaces and even tried using TRIM, but nothing seems to work. Is there a known limitation or best practice when using VLOOKUP with named ranges that contain merged cells? Any advice on how to handle this situation would be greatly appreciated! For context: I'm using Excel on Linux. How would you solve this? I recently upgraded to Excel stable. Thanks for any help you can provide! I'm developing on Debian with Excel. Any ideas how to fix this? What's the correct way to implement this? The project is a service built with Excel. My development environment is macOS. Is there a better approach?