Multiple if condition excel, Matrix structure

user_15118626 0
Viewed 84
Box type Box type Box type Box type
BinLoc 810 811 911 822
S1 2 0 1 0
S2 4 2 2 1
S3 12 6 6 3
S4 24 12 12 6
R1 48 24 24 12
R2 96 48 48 24

Well 810,811,911,822 are box type and s1,s2,s3,s4,r1,r2 are bin location, I need to find out which bin location should I choose based on the input (Ex: Box type = 810 and No of boxes = 5) since 5 lies within 4 to 12 in 810 box type the bin location is S3

Ex data

So I tried multiple if condition I couldn't do it. Please help

SUP INVOICE NUMBER Qty Box Type Bin Loc
92 ACAMP206186 2 810
92 ACAMP206186 2 811
92 ACAMP206186 2 822
92 ACAMP206186 3 810
92 ACAMP206186 3 811
92 ACAMP206186 4 810
92 ACAMP206186 4 811
92 ACAMP206186 4 822
92 ACAMP206186 5 810
92 ACAMP206186 5 811
92 ACAMP206186 5 911
92 ACAMP206186 6 810
92 ACAMP206186 6 811
92 ACAMP206186 6 822
92 ACAMP206186 7 810
92 ACAMP206186 7 911
92 ACAMP206186 8 810
92 ACAMP206186 8 811
92 ACAMP206186 9 811
92 ACAMP206186 10 810
if-statement excel nested arrays
1 Answers

If I understand question correctly then you can try below formula.

=XLOOKUP(I2,INDEX($B$3:$E$8,,MATCH(J2,$B$2:$E$2,0)),$A$3:$A$8,,-1)

XLOOKUP() is only available to EXCEL-365. For older version may try below formula but Box Type data must be sorted.

=INDEX($A$3:$A$8,MATCH(I2,INDEX($B$3:$E$8,,MATCH(J2,$B$2:$E$2,0)),1))

enter image description here

user_55147470
Related