vendredi 6 août 2021

Why my index match formula is not working?

enter image description here

Column C (Box qty) and D (Box type) are my inputs here. Using index match i'm trying to retun the bin location (S1,S2,S3..etc) in column I by checking the Box type (810,811,911,822) and qty inside the table J to M which is pre fixed.

I used -1 in the end to get the next nearest lookup. Ex: ( if my box type is 810 and my box qty is 5 it should return S3 from bin location. I tried 1 before but it's returning the previous value S2. Could anyone tell me what's wrong with the formula or any alternative to return the next nearest bin location?

Note: If the box qty same inside the table it should return the bin location that is matching exactly, the qty in between two numbers should return the next nearest bin location.

Aucun commentaire:

Enregistrer un commentaire