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?, "")
)))
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.
Yes, I find this behaviour of IFS unexpected too.
$F$2:$P$35=B3 is a 2D array, $E$2:$E$35 is a 1D array.
It still amazes me that IFS decides to scan $F$2:$P$35=B3 BY ROW and return the corresponding value in $E$2:$E$35 when the IF condition is met.
Nice simplifying PeterBartholomew1 's formula with IFS here!
- Thank you very much, thats really useful
