Forum Discussion
Lookup Formula with multiple column
I liked the way you dealt directly with the 2D totals array. MMULT works well for the OR condition.
I have used your workbook for a couple more dynamic array formulas but they are probably moving even further from the OP's comfort zone!
= MAP(CL, LAMBDA(CL₁, LET( ORλ, LAMBDA(x, OR(x)), found?, BYROW(totalling=CL₁, ORλ), FILTER(rowNo, found?, "") )))or with insider beta
= MAP(CL, LAMBDA(CL₁, LET( found?, BYROW(totalling=CL₁, OR), FILTER(rowNo, found?, "") )))Thanks for the demonstration of TOCOL function! Still getting myself familiar with MS365 here.
PeterBartholomew1 and Carlyn1505 Probably the simplest method for this scenario with MS365 is to use the TOCOL and IFS method:
=TOCOL(IFS($F$2:$P$35=B3, $E$2:$E$35), 2)Or with the MAP function to do the entire GL range in one shot:
=MAP(B3:B35, LAMBDA(v, TOCOL(IFS(F2:P35=v, E2:E35), 2)))The IFS function returns #NA by default if the logical test evaluates to FALSE, so there is no need for the NA() function (like with IF). The TOCOL function then eliminates all errors, with the optional [ignore] argument set to 2, returning only the matching value as the final result.
Note: if there's a chance that more than one match may be found in the lookup range, wrap the TOCOL/IFS combo inside the TAKE function to return the first result only. The optional [scan_by_column] argument (TOCOL) can also be used to search top-to-bottom, left-to-right, if desired. Also, IFERROR can be used to eliminate any #CALC! errors in the event that no match is found.
Nice! I don't think I had every seen that as feature of IFS, never mind exploited it. I latched onto the idea of unpivoting the cross-tab data, knowing that I could process it from there. From there on, I had blinkers on and simply missed the fact that much of the processing could be done on the 2D array.
- Thank you so much Rachel, this has worked although I am not going to pretend that I know how! 🙂
- Thanks for your feedback !
