Inventory Formula Using Index Match with IF Function to return Text Values under Multiple Criteria

[ak_254]

Hi all,

I am trying to figure out the best way to incorporate an inventory reporting formula to state the "Inventory Level" of an item based on recent/all purchase orders.

In Sheet 1 it will contain all inventory currently being held with key information such as SKU and Inventory Level of item.

In Sheet 2 it will contain all recent and past purchase orders that were placed for said inventory item with key information such as SKU, Order Number, Order Received, and Order Aging (This will be based on the field "Order Received.. if Yes is selected no number is calculated, if No is selected todays date minus the order date is calculated and a number is generated)

For this formula I would want to be able to search the SKU from Sheet 1 on Sheet 2 under multiple criteria and return text values based on if these criteria are met

1) Search to find all rows to match SKU from Sheet 1 with Sheet 2

If a Sku(s) does match on Sheet 2..

2) Determine if there is a value in Order Aging..

• if there is a number populated in the cell return the value "On Backorder" in Sheet 1 under "Inventory Level"
• If there is no number populated in the cell return the value "In Stock" in Sheet 1 under "Inventory Level"

If a Sku(s) does not match Sheet 2..

3) If there is no Sku match return the value "Out of Stock" in Sheet 1 under "Inventory Level"


Sheet 1: Inventory Status

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU[/TD]
[TD]Item[/TD]
[TD]Inventory Level[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]111[/TD]
[TD]Cables[/TD]
[TD]Should State "In Stock"[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]222[/TD]
[TD]Keyboards[/TD]
[TD]Should State "On Backorder"[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]333[/TD]
[TD]Mouses[/TD]
[TD]Should State "Out of Stock"[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2: Order Forms

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU[/TD]
[TD]Item[/TD]
[TD]Order Date[/TD]
[TD]Order Number[/TD]
[TD]Order Received [/TD]
[TD]Order Aging[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]111[/TD]
[TD]Cables[/TD]
[TD]1-1-18[/TD]
[TD]001[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]222[/TD]
[TD]Keyboards[/TD]
[TD]1-2-18[/TD]
[TD]003[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]222[/TD]
[TD]Keyboards[/TD]
[TD]1-8-18[/TD]
[TD]001[/TD]
[TD]No[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Any help or point in the right direction would be greatly appreciated!!

 

[stunnrock]

=IF(ISNA(MATCH($A2,Sheet2!$A$2:$A$30,0)),"Out of Stock",IF(SUMIF(Sheet2!$A$2:$A$30,$A2,Sheet2!$F$2:$F$30)>0,"On Backorder","In Stock"))

 

[ak_254]

Wow this is exactly what I was looking for.. can't thank you enough!!

 

[stunnrock]

You're welcome. Glad to help.