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

    • peiyezhu's avatar
      peiyezhu
      Bronze Contributor
      Jun 02, 2023

      benross94 

       

      =transpose(filterxml(webservice("http://e.anyoupin.cn/eh3/?preg_match_all~Truck\s*\d+~Truck 2 Crew, Firstname1 Lastname 1, Truck 3 Crew")
,"/document/items[1]/items"))
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 22, 2023

      benross94 

      To simplify formulae I'd use helper columns. For example

      and

      in D2
=B2
and drag down

in E2
=SUBSTITUTE(D2,H2,"", 1)
and drag down and to the right

      After that you may hide these columns. Or place the block somewhere to the right outside your data.

      With that

      in H2
=TRIM(MID(D2,SEARCH("truck",D2),8))
and drag down

in I2
=IFERROR(  TRIM(MID(  SUBSTITUTE( D2, H2, ""),  SEARCH("truck",SUBSTITUTE( D2, H2, "")),8)), "" )
and drag to the down and right

      Finally in M2

      =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($L2, 'TRUCK CREWS'!$C$3:$P$3, 0) ) = $H2 )*($H2 <> "") +
                    ( INDEX( 'TRUCK CREWS'!$C$4:$P$19, 0, MATCH($L2, 'TRUCK CREWS'!$C$3:$P$3, 0) ) = $I2 )*($I2 <> "") +
                    ( INDEX( 'TRUCK CREWS'!$C$4:$P$19, 0, MATCH($L2, 'TRUCK CREWS'!$C$3:$P$3, 0) ) = $J2 )*($J2 <> "") +
                    ( INDEX( 'TRUCK CREWS'!$C$4:$P$19, 0, MATCH($L2, 'TRUCK CREWS'!$C$3:$P$3, 0) ) = $K2 )*($K2 <> "")
                ),
            COLUMN() - COLUMN($L$1)
        )
    ),
    ""
)

      and drag to the right and down.

      Above assumes you have max 4 trucks. Just an idea, you may play with other variants of helper columns. Or update on 365, life will be much easier.

       

      In addition, perhaps you don't need text in column B and would like to pickup all truck crews for given day?

      • 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!

Resources