Forum Discussion

Copper Contributor

Sep 26, 2023

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

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...

excel

Formulas and Functions

JosWoolley

Iron Contributor

Sep 26, 2023

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

Pjain87

Copper Contributor

Sep 26, 2023

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.