Forum Discussion
Solved
xlookup with multiple criteria including partial text match
hi need help in extracting values
in cell S10, im getting H6=1657 but i should be getting H4=1569
how should the xlookup function be fixed?
your other conditions (G:G=R10) use the whole column as reference, so try adjusting LEFT(D4:D31) to LEFT(D:D,1)
3 Replies
Hi SP3990,
Use below formula in cell S10 and drag it down.
=XLOOKUP(1, ($B$4:$B$20 = $P$10) * (LEFT($D$4:$D$20, 1) = $P$11) * ($G$4:$G$20 = R10), $H$4:$H$20)or you can use this formula (which is dynamic formula and no need to drag it down).
=BYROW(R10:R13, LAMBDA(r, XLOOKUP(1, (B4:B20 = P10) * (LEFT(D4:D20, 1) = P11) * (G4:G20 = r), H4:H20)))Change the range in formula as per you need.
HTH
IlirU
The ranges don't have the same size.
your other conditions (G:G=R10) use the whole column as reference, so try adjusting LEFT(D4:D31) to LEFT(D:D,1)
