Forum Discussion
Can VLOOKUP return multiple cells found in a single row?
You say
I know merging can be problematic, but I do not know how else to organize the data table.
And with that acknowledgment you've put your finger on the problem. A table, an Excel table, does NOT work with merged rows for such things as Invoice numbers or LOT, etc. It may "look nice" to the human eye, but it totally interferes with the computer's abilities--which are many--to parse the data as you now want to do.
How else to organize? Repeat the Invoice number and Sales contract...whatever needs to be done. Basically, you need to reorganize your data into a standard Excel Table. Then you'll probably find that FILTER (a new relatively new function that does require the most current version of Excel) will work wonders for you. But start with reorganizing your data.
Herewith, some references for you to research.
On Tables: https://exceljet.net/glossary/excel-table
On FILTER: https://www.youtube.com/watch?v=9I9DtFOVPIg
