Forum Discussion

benross94's avatar
benross94
Copper Contributor
Apr 16, 2023
Solved

Need help Complex Look Up Formula

Hello, I am looking for help with https://1drv.ms/x/s!AsAcHcm1UiD3drNnfXaBu7aPx5M?e=DwVlT1 The task: In the CRM Report tab, columns F onward, I want to pull the names of people who were on a given...
excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Apr 17, 2023

    benross94 Your formula is working fine to me. What error do you get? You may simplified the formula using nested FILTER() function. Try-

    =TOROW(FILTER('TRUCK CREWS'!$B$4:$B$19,FILTER('TRUCK CREWS'!$C$4:$P$19,'TRUCK CREWS'!$C$3:$P$3=E2)=D2))

    By the way, what is your excel version?

     

benross94's avatar
benross94
Copper Contributor
Jun 01, 2023

SergeiBaklan , thank you for the continued help. I need assistance with a very similar formula, but for some reason when I tried you use your formula you create in a new tab with slightly different cell references, it doesn't work, even if I try to change the cell references in the formula. In the tab "Res Donation", are you able to create a formula that once again pulls the names of all people on a given truck (column I) on a given date (column F) in the tab "Res Donation", starting in column O? See file attached.

Thank you!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 02, 2023

benross94 
That's significantly different data structure compare to previous sample. Perhaps like this

=IFERROR(
    INDEX(
        'TRUCK CREWS'!$B$4:$B$23,
        AGGREGATE(
            15,
            6,
            (ROW('TRUCK CREWS'!$B$4:$B$23) - ROW('TRUCK CREWS'!$B$3)) /
                        ( INDEX( 'TRUCK CREWS'!$E$4:$R$23, 0, MATCH($F15, 'TRUCK CREWS'!$E$3:$R$3, 0) ) = $I15 )*($I15 <> ""),
            COLUMN() - COLUMN($N$1)
        )
    ),
    ""
)

Please check in attached.

  • benross94's avatar
    benross94
    Copper Contributor
    Jun 02, 2023
    Thank you!

Resources