Forum Discussion
Need help Complex Look Up Formula
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?
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 rightAfter 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 rightFinally 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?
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!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.
- Thank you!