Hi all! 👋😊

I am trying to find a solution that will let me use the values in a spill range to query a table and return related values from another column. I attempted to do this using this formula: FILTER(INDEX(Table1,,7),INDEX(Table1,,9)=$F3#,””) – where $F3# is the spill range, its values are in column 9 of Table1 and the required value is in column 7 of the same table. The formula results in an error and I was unable to resolve it to a working state.

Instead, I went for a clunky but simple query that references a bunch of rows where the spill will occur. It fetches the values that I need but is not dynamic… ideally the solution would expand and contract with the spill range. I’m sure it’s possible, but just beyond the reach of my current skill set or knowledge so created a sample workbook with dummy data, hoping it might help someone suggest a more elegant solution 🤞

Thanks in advance, Andy!

​Hi all! 👋😊 I am trying to find a solution that will let me use the values in a spill range to query a table and return related values from another column. I attempted to do this using this formula: FILTER(INDEX(Table1,,7),INDEX(Table1,,9)=$F3#,””) – where $F3# is the spill range, its values are in column 9 of Table1 and the required value is in column 7 of the same table. The formula results in an error and I was unable to resolve it to a working state. Instead, I went for a clunky but simple query that references a bunch of rows where the spill will occur. It fetches the values that I need but is not dynamic… ideally the solution would expand and contract with the spill range. I’m sure it’s possible, but just beyond the reach of my current skill set or knowledge so created a sample workbook with dummy data, hoping it might help someone suggest a more elegant solution 🤞 Thanks in advance, Andy!  Read More

​