Pull a third value on the basis of two criterias using INDEX and MATCH in excel

Question

Needed some help with the INDEX match formula, here goes..

Have an excel with two sheets, - Data sheet contains an inventory master of sorts where.. we can see each item being displayed with multiple batches in with each of their own quantities depending on where they're stored.. - Sheet 1 is an order form in which my end user would like to get the exact batch of a product on the basis of two criterias.. Criterias being - product number and qty match to fullfill..

Data - Current Inventory

Item Quantity Batch
ABD  10       11223a
ABD  15       24589r
DFG  5        T45678
DFG  67       ghytu8
FGH  10       thnh67
FGH  10       huip78

Sheet 1 - Order form
Item  Quantity  Batch
ABD   8         
DFG   4
DFG   10 
FGH   10

I have now tried the following formula for INDEX/MATCH in the batch field for sheet 2 but it does not seem to work.. please advise..

=INDEX(Data!C12550:R19719,MATCH(1,(Data!C12550:C19719=Sheet1!A2)*(Data!D12550:D19719=Sheet1!B2),0),7)

Note in the actual sheet the batch numbers in sheet 1 lie on the 7th Column thus the column referenced at the end is 7..

Thank you.

"it does not seem to work" is not helpful for finding an error. What exactly is happening? Does it return an error or a value that is not valid? Is it working for some values or not working at all? — Egan Wolf
You should use absolute reference in your formula as =INDEX(Data!$C$12550:$R$19719,MATCH(1,(Data!$C$12550:$C$19719=Sheet1!A2)*(Data!$D$12550:$D$19719=Sheet1!B2),0),7) and this is an array formula so after entering it press Ctrl+Shift+Enter. If issue still persist, give details. — Mrig
Sorry I missed out an important point in the query, the order form value should pick of a field from the data sheet where the quantity matches exactly or is less than that of the data field.. For e.g - if in the order the required quantity of item ABD is 11 then it should pick the qty of from line 2 where the available qty is 15.. example 2 - if the qty required in the order for item FGH is 10 then it should end up taking the first available field.. — user2269061

Thomas Thomas · Accepted Answer · 2017-07-14T15:16:44

I have done something like this before.
I would go like this.
Add a helper column to your datasheet where you concatenate Item and quantity.
In your Order form you can simply index the batch number only and do a match of the concantenated Item & Quantity in your helper column.

This is the Data sheet setup

Then On the order form:

Hope this helps.

Pull a third value on the basis of two criterias using INDEX and MATCH in excel

2 Answers