Forum Discussion

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

Need help Complex Look Up Formula

Hello, I am looking for help with this document.

The task: In the CRM Report tab, columns F onward, I want to pull the names of people who were on a given Truck # (column D) on a given date (column E), using the information TRUCK CREWS tab. My attempt at this formula is in cell F2.

 

I think my current formula should be working but it's not. I am looking for someone to either correct it or come up with another way to do it that works. Thank you!

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?

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    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
      May 22, 2023
      Hello,

      This stopped working for some reason, and I think it is because I am using 2019 home and student excel. Is there a different formula one could use for this?
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        May 22, 2023

        benross94 

        For 2019

        In F1

        enter the formula

        =IFERROR(
    INDEX(
        'TRUCK CREWS'!$B$4:$B$19,
        AGGREGATE(
            15,
            6,
            (ROW('TRUCK CREWS'!$B$4:$B$19) - ROW('TRUCK CREWS'!$B$3)) /
                (
                    INDEX(
                        'TRUCK CREWS'!$C$4:$P$19,
                        0,
                        MATCH($E2, 'TRUCK CREWS'!$C$3:$P$3, 0)
                    ) = $D2
                ),
            COLUMN() - COLUMN($E$1)
        )
    ),
    ""
)

        and drag it to the right and down.

        Please see in attached.

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        Apr 18, 2023
        If it works then please tick mark the answer to treat the thread as solved.
  • FikturFox's avatar
    FikturFox
    Brass Contributor
    Apr 17, 2023
    Perhaps like this?
    =IFERROR(TOROW(IF(XLOOKUP(E2;'TRUCK CREWS'!$C$3:$P$3;'TRUCK CREWS'!$C$4:$P$19;"")=D2;'TRUCK CREWS'!$B$4:$B$19;NA());3);"")

Resources