Forum Discussion
Search matching values across sheets and return a range of data from sheet matching value found on
Hello,
I am trying to do the below:
If value A1 on Sheet 1 matches any C3 values on Sheet 2 through Sheet 5 then return all values from range D3:G6 from the sheet with the matching C3 value to Sheet 1 - beginning in the formula cell.
Is this possible? If so any recommendations on how to accomplish it?
Thank you!
This uses 365 to build an array of functions (thunks), each element of which will return an array when evaluated. It then builds the lookup array by stacking values from each sheet within the 3D range. XLOOKUP is used to return the required function for evaluation.
= LET( returnArraysϑ, VSTACK( LAMBDA(Sheet2!D3:G6), LAMBDA(Sheet3!D3:G6), LAMBDA(Sheet4!D3:G6), LAMBDA(Sheet5!D3:G6)), lookupArray, VSTACK(Sheet2:Sheet5!C3), returnϑ, XLOOKUP(value, lookupArray, returnArraysϑ), returnϑ() )I wouldn't normally show a direct cell reference on any worksheet but the formula most likely appears pretty alien already without playing 'guess the name'.
If you have access to VSTACK, perhaps...
=LET(stack, VSTACK(Sheet2:Sheet5!C3:G3), DtoG, DROP(stack, , 1), c, TAKE(stack, , 1), FILTER(DtoG, c = A1))In B1:
=IFS('Sheet 2'!C3=A1, 'Sheet 2'!D3:G6, 'Sheet 3'!C3=A1, 'Sheet 3'!D3:G6, 'Sheet 4'!C3=A1, 'Sheet 4'!D3:G6, 'Sheet 5'!C3=A1, 'Sheet 5'!D3:G6, TRUE, "")
Replace the sheet names with the actual names (if necessary).
