Log In
Register
Home
June 2, 2020
Offline
I want to choose the lowest supplier/s name from the table as below:
| # | Item No. | Item Name | Lowest Price | Lowest Supplier Name | A | B | C | D | E | F | G |
| 1 | 3230 | Book | 2 | 8 | 2 | 5 | 6 | 8 | 9 | 7 | |
| 2 | 16 | Pen | 7 | 13 | 10 | 9 | 8 | 7 | 11 | 12 | |
| 3 | 3354 | Keyboard | 1 | 12 | 1 | 1 | 1 | 1 | 6 | 15 | |
| 4 | 265 | Screen | 4 | 4 | 9 | 8 | 18 | 18 | 7 | 88 |
See Answer
Trusted Members
December 20, 2019
Offline
Much better if you add a workbook as an example rather than text.
I assume the letters are the supplier? This will get the letter (you could use a HLOOKUP but i prefer index & match)
=INDEX($F$1:$L$1,MATCH(MIN(F2:L2),F2:L2,0))
It will only find the first one in the list
Purfleet
Answers Post
June 2, 2020
Offline
thanks a lot for your answer & solving.
June 2, 2020
Offline
I need function to shows the cheapest Company name?
Thanks in advance
Trusted Members
December 20, 2019
Offline
So the supplier is at the top row and the Maker of the product is to the left of the price?
Personally i would recommend changing the layout of the data if were you as this is not a proper dataset and you could end up with more issues going forward.
That said if you are stuck with this you could use =IFERROR(INDEX($G2:$L2,,MATCH(MIN($G2:$L2),$G2:$L2,0)+1),"-")
Purfleet
June 2, 2020
Offline
Thanks for your efforts.
What is your recommendation regards the changing the layout of the data for this table because i have a lot of suppliers and each time have new suppliers?
October 5, 2010
Offline
Hi Moutaz,
The best layout is a tabular format where there is a column for every type of data or 'thing to be measured' and a row for each record or transaction.
If you look on Sheet3 of the attached file you'll see I've created a table with your data.
This layout allows you to easily use pivot tables, formulae, Power Query etc as needed.
On Sheet2 I've created a basic PivotTable with a filter for the Item. The lowest price is highlighted in green with conditional formatting. You could create another CF rule that highlights the highest price if that was useful to you.
The formulae on Sheet3 looks-up the lowest price, company and supplier from the table. Using the data validation list in cell B14, just change the Item type.
Hopefully you will see that a tabular layout is the best structure for data as it allows so much flexibility in the reporting choices you have.
Regards
Phil
The following users say thank you to Philip Treacy for this useful post:
Purfleet
1 Guest(s)
