Calling multiple value from another sheet based on a single value in a sheet

[Pjain87]

Hi Community,

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?

Thanks!

[#2]

@Pjain87 

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(β,ε)))
)

[#3]

@Pjain87 

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?"

[#4]

Thank you @Peter Bartholomew, this is brilliant!
Really appreciate your assistance here...I wouldn't about this capability in excel

[#5]

Thank you JosWoolley. This is amazing and it worked. In fact, I was able to get the same results with both the solutions.
Brilliant!! Really really appreciate it.