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?

     

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 21, 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
      Diamond Contributor
      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.

      • benross94's avatar
        benross94
        Copper Contributor
        May 22, 2023

        SergeiBaklan 

        Thank you so much. Your formula fixed that issue, however I have two additional questions that would help me a lot if you are able to help.

         

        1. In the updated example file attached, in tab CRM "Report New", I have added multiple Truck # columns in D:G because some times there are multiple truck #s (up to 4) listed in a cell in column B. I am looking for a formula that will pull all truck numbers listed in column B.

         

        For example, B2 says "Truck 2 Crew, Firstname1 Lastname 1, Truck 3 Crew". I would like D2 to output Truck 2 and E2 to output Truck 3

         

        I originally had the following formula but it no longer works in 2019. 

         

        =_xlfn.LET( _xlpm.a,_xlfn.TEXTSPLIT(B2,,", "), _xlpm.b,ISNUMBER(SEARCH("Truck",_xlpm.a)), _xlpm.c,_xlfn._xlws.FILTER(_xlpm.a,_xlpm.b), _xlpm.d,SUBSTITUTE(_xlpm.c," Crew",""), TRANSPOSE(_xlpm.d))

         

        2. If, for example, there are two trucks listed, then I want to be able to pull all names on all trucks. I tried doing this by copying the original formula you created rightward to column M and changing the reference from the first truck # to the second truck #, column D to column E, but this does not work. Is there something I am doing wrong?

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

    Harun24HR This worked, thank you!

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Apr 17, 2023
      If it works then please tick mark the answer to treat the thread as solved.

Resources