Pull a third value on the basis of two criterias u

问题:

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.

回答1:

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.

回答2:

This issue has been resolved now... I figured out the correct formula, however it refused to work between 2 different sheets due to some circular reference error that wasn't going away..

So ive made a separate section on the same data sheet and applied the following formula which works perfectly!

> =INDEX(C:R,MATCH(1,(C:C=W3)*(R:R>=Y3)*(D:D>=X3),0),3)

This now has multiple criteria's that I can work with..