Sep 25 2023 09:34 PM
Sep 25 2023 09:34 PM
I have a spreadsheet with multiple tabs. In one tab I have a set of unique values as:
And in another tab, I have multiple corresponding values.
I want to call values from tab 2 into tab 1, something like this:
Is it possible with excel formulas?
Sep 25 2023 10:55 PM - edited Sep 25 2023 11:01 PM
Assuming first sheet's entries are in A1:A3 and second sheet's details are in A1:B5:
=LET( ε,A1:A3, α,Sheet2!A1:A5, β,Sheet2!B1:B5, φ,DROP(REDUCE("",ε,LAMBDA(γ,η,VSTACK(γ,TOROW(SORT(IF(β=η,α)))))),1), TAKE(IF(ISLOGICAL(φ),"",φ),,MAX(COUNTIF(β,ε))) )
Sep 26 2023 03:15 AM
This is merely the tip of the iceberg that is the "Nested arrays are not supported" problem that results from a monumentally poor bit of decision making on behalf of Microsoft.
As one gets used to using Lambda functions, my experience is that one increases the scope of functions to apply to larger and larger elements of the solution until one goes past the point at which arrays of arrays are required at least 50% of the time.
The reason @JoeMcDaid gave was that Excel never had supported arrays or arrays or arrays of ranges so, for reason of backward compatibility; it is as it is! The problem with that argument is that Excel had never claimed to be a Turing complete programming language (in fact, it was a programming language that intentionally denied its nature). Now that the Excel worksheet formula language is Turing complete, it is unfortunate that the attempt to ensure compatibility with the past blights the future!
The best solution I have come up with to date for this class of problem is to write a recursive Lambda function that acts as a helper function. Rather than simply stacking the results as they arise, the function bisects the problem until a single case is reached. The partial results are then combined pairwise up the tree until the result is returned.
Why endure such complexity? The reason is that it can make the difference between a calculation time of over 3½min and <1sec. So, until Microsoft does something about the problem:
BMAPλ(X, Fnλ) =LET( n, ROWS(X), Y, IF( n > 1, LET( ℓ, n - QUOTIENT(n, 2), X₁, TAKE(X, ℓ), X₂, DROP(X, ℓ), Y₁, BMAPλ(X₁, Fnλ), Y₂, BMAPλ(X₂, Fnλ), IFERROR(VSTACK(Y₁, Y₂), "") ), Fnλ(X) ), Y )
The actual function can be passed to the helper function as a Curried Lambda function, in this case,
Filterλ(list, value) = LAMBDA(α, TOROW(FILTER(list, value = α, "")))
The final worksheet formula using the Bisection MAP helper function is
= BMAPλ(CompanyName, Filterλ(product, brand))
The question is "Should it really be this difficult?"
Sep 26 2023 07:40 AM
Thank you @Peter Bartholomew, this is brilliant!
Really appreciate your assistance here...I wouldn't about this capability in excel
Sep 26 2023 07:41 AM