Populate Price of Product from PO# in Inventory Summary

Copper Contributor

I have an inventory tracking worksheet, it has multiple tables, the inventory received, the service requests and the remaining inventory. I have all the columns/formulas working, except one. I need to populate the price of the inventory item in inventory remaining, my only problem is that on inventory received there are entries for each piece of equipment, where as the remaining inventory sheet rolls the like items up into a single entry.

lordbyronxxiv_0-1650465100668.pnglordbyronxxiv_1-1650465144897.png

They need to also be matched up by item code and PO# as different PO's may have different prices for the same item. In the above instance in the remaining inventory sheet I need the price of the Latitude 7420 only once even though there are 7 latitude 7420's in the inventory in sheet. The price of an item is always the same in the same PO, it only changes between different PO's, so I will need to use the PO and item code as search criteria.

1 Reply
Try this:
=IFERROR(INDEX(Received_Table[Cost],MATCH([@[PO#]]&"~"&[@[ITEM]],Received_Table[PO#]&"~"&Received_Table[[Item Name]],0)),"NA")
basically using a simple INDEX( MATCH() ) where the MATCH is combining the PO# & the ITEM. I'm inserting the "~" just in case an Item Name starts with a number so PO123 & 123item doesn't pick up PO123123 & item (I know it is unlikely but better to be safe)
I also assumed they are formatted as Tables and used structured references. If not, I recommend you do format them as tables but if you don't you can replace the structured references with sheet!range references.