• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

I want to choose the cheapest supplier name from the table|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / I want to choose the cheapest supplier name from the table|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…I want to choose the cheapest suppl…
sp_PrintTopic sp_TopicIcon
I want to choose the cheapest supplier name from the table
Avatar
Moutaz ElAbasi
Member
Members
Level 0
Forum Posts: 6
Member Since:
June 2, 2020
sp_UserOfflineSmall Offline
1
June 2, 2020 - 11:28 pm
sp_Permalink sp_Print

I want to choose the lowest supplier/s name from the table as below:test-1.pngImage Enlarger

# 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
sp_PlupAttachments Attachments
  • sp_PlupImage test-1.png (56 KB)
sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
June 3, 2020 - 4:39 am
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Moutaz ElAbasi
Member
Members
Level 0
Forum Posts: 6
Member Since:
June 2, 2020
sp_UserOfflineSmall Offline
3
June 3, 2020 - 9:36 pm
sp_Permalink sp_Print

thanks a lot for your answer & solving.

Avatar
Moutaz ElAbasi
Member
Members
Level 0
Forum Posts: 6
Member Since:
June 2, 2020
sp_UserOfflineSmall Offline
4
June 4, 2020 - 12:31 am
sp_Permalink sp_Print

I need function to shows the cheapest Company name?

 

Thanks in advance 

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
5
June 4, 2020 - 2:16 am
sp_Permalink sp_Print

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

Avatar
Moutaz ElAbasi
Member
Members
Level 0
Forum Posts: 6
Member Since:
June 2, 2020
sp_UserOfflineSmall Offline
6
June 4, 2020 - 6:52 pm
sp_Permalink sp_Print

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?

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1515
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
7
June 9, 2020 - 3:26 pm
sp_Permalink sp_Print

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
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Chris Warren
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27248

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.