Forum Discussion
Populate Price of Product from PO# in Inventory Summary
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 t...
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.
=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.