I am trying to return the whole row of product data from a table based on the respective products' minimum costs...
- I have a number of suppliers, supplying the same products, at varying wholesale pricings.
- I have 3 criteria, and once the products meet them, I what to return the whole row of the lowest priced product.
- I can't us MINIFS and then XLOOKUP as the MINIFS identifies the lowest price matching the 3 criteria, but then XLOOKUP only uses that minimum price as its look up value, and other non-complying products, but with the same complying product price, gets values on that row returned.
I've attached an abridged version of may data sheet and results sheet.
PC Excel 365.
Welcome any solutions and or work arounds.
Many thanks, love the videos, so succinct. Thank you. Edward.
Hi Edward,
I had a good look at your schedule and believe that XLOOKUP is not going to do the job. Perhaps you want to consider Power Query. Then it becomes a fairly easy task.
But I thought of a formula based solution as well, using INDEX and XMATCH. But you need to start by sorting the DataTable in ascending order (assuming that the table is already sorted by ID), based on Wholesale_Price.
Your example contains two rows for Part5/Red/40 each with the lowest price of $230. You seem to want to select the supplier with the lowest ID. Sorting as described above will take care of that.
Both the PQ and formula based solutions are included in the attached file. I hope you'll find them useful.
How about using FILTER:
=MIN(FILTER(DataTable[Retail_Price],(DataTable[Product]=[@ProductResult])*(DataTable[Colour]=[@ColourResult])*(DataTable[Weight]=[@WeightResult])))
Mynda
