modify actual formula with if based of 4 digit criteria of another column

[JEAN1972]

I have a workbook that extract quantities in column Q depending on data of columns F and G and AI .

That is if column AI shows "U","m2","2U"} then extract qty from column O otherwise if it shows L in column AI then multiply value in column F and G .

IF kg IN COLUMN AI then do nothing.

=IF(AI26="L",IFERROR(1/(1/(F26*G26)),""),IF(OR(AI26={"U","m2","2U"}),O26,""))

But I need to add a special criteria in the formula which is based on column B that is the first four digit only starts with 2106 or 3923 or 3924, then extract quantity in column O (note that in column Ai THE UNIT OF MEASURE IS kg but only applies for 2106 3923, 3924 .

Needs assistance to revised the above formula.

sample expected results shown at rows at 24 to 26 .

[Glenn Kennedy]

One way, simplay adding to your formula:

=IFERROR(LOOKUP(1000,SEARCH({"2106","3923","3924"},B2),O2*{1,1,1}),IF(AI2="L",IFERROR(1/(1/(F2*G2)),""),IF(OR(AI2={"U","m2","2U"}),O2,"")))

copied down.

[JEAN1972]

Hi Glenn

Working as requested thank you but just a little explanation meaning for O2*{1,1,1}

Richard working but those for litres (L) , not giving expected results giving results of column instead of multiplication of column F and G

[Richard Buttrey]

Maybe

=IFERROR(IF(MATCH(LEFT(B2,4),{"2106","3924","3923"},FALSE)>0,O2),IF(F2="","",O2))

[Glenn Kennedy]

Richard... that's neat... even if it doesn't give the required result. This modification does:

=IFERROR(IF(MATCH(LEFT(B2,4),{"2106","3924","3923"},FALSE)>0,O2),IF(F2="","",F2*G2))

but both yours and my modification of yours fail at row 27... and I don't understand why

[Glenn Kennedy]

Jean, it's simply a way of putting O2 into an array 3 times, to permit comparison with the 3 search terms.

[JEAN1972]

ok thank you for explanation