Forum Discussion
Index Match problem
- Mar 26, 2019To remedy the possible occurrence of duplicates, I suggest this formula in Inventory!A16:
=IFERROR(INDEX('Original Catalog & Customers'!B$6:B$16,
SUMPRODUCT(MAX(ROW('Original Catalog & Customers'!C$6:I$16)*
('Original Catalog & Customers'!C$6:I$16=B16)))-5),
"No such")
SergeiBaklan I figured an abbreviated spreadsheet would be easiest, which I have attached. I have an inventory workbook that lists out all of the inventory we have on hand for finished products. The inventory includes our own inventory, but also products that have been re-labeled for our customers, but in reality are one of our original catalog id numbers. I basically want to be able to look up the customers item number in an array and then bring back the original catalog id that is associated with that customer's item number. I've attached a shortened version. I have been trying to put in a formula either to the right or left (highlighted in yellow). It seems like it shouldn't be that complicated, but I have yet to figure out what I am doing wrong.
=IFERROR(INDEX('Original Catalog & Customers'!B$6:B$16,
SUMPRODUCT(MAX(ROW('Original Catalog & Customers'!C$6:I$16)*
('Original Catalog & Customers'!C$6:I$16=B16)))-5),
"No such")