+ Reply to Thread
Results 1 to 7 of 7

Help needed with VLOOKUP OR INDEX

  1. #1
    Registered User
    Join Date
    02-10-2017
    Location
    London
    MS-Off Ver
    2007
    Posts
    5

    Help needed with VLOOKUP OR INDEX

    We are working on a data sheet where we need to find the lowest price sold for each product and to the customer it was sold:
    We have Products e.g. Product A, Product B, Product C,..... and this product is sold for different prices to different business customers.


    When we use the formula it just gives us the lowest value of the table and not of that particular product. We know that we are missing something as the formula just work like MIN formula. I have attached the excel sheet with the formulas:

    Product Price Customer
    Product A 7 Customer 1
    Product C 8 Customer 1
    Product A 8 Customer 2
    Product B 5 Customer 1
    Product A 12 Customer 3
    Product B 3 Customer 2
    Product D 5 Customer 3
    Product C 9 Customer 2
    Product B 6 Customer 3
    Product D 6 Customer 1


    Customer 1 3 .=INDEX(MIN(B2:B11),MATCH(A14,A2:A11,0))
    Customer 2
    Customer 3
    Customer 4
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Help needed with VLOOKUP OR INDEX

    what is it you want, the customer or the product?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    02-10-2017
    Location
    London
    MS-Off Ver
    2007
    Posts
    5

    Re: Help needed with VLOOKUP OR INDEX

    Hi,
    Thank you for looking into this and helping us, I have redone the table to make it more clear.

    Product Price Customer
    Product A 7 Customer 1
    Product C 8 Customer 1
    Product A 8 Customer 2
    Product B 5 Customer 1
    Product A 12 Customer 3
    Product B 3 Customer 2
    Product D 5 Customer 3
    Product C 9 Customer 2
    Product B 6 Customer 3
    Product D 6 Customer 1


    Product A 3 .=INDEX(MIN($B$2:$B$11),MATCH(A14,$A$2:$A$11,0))
    Product B #REF!
    Product C #REF!
    Product D #REF!


    As we heave 'Product A' 3 times in above table sold for 3 different prices
    The formula should show us the lowest price for 'Product A' but it is showing us the lowest price of all Products.
    also when I drag down the formula it give me above error
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Help needed with VLOOKUP OR INDEX

    You can use this array formula:

    =MIN(IF($A$2:$A$11=A14,$B$2:$B$11))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Help needed with VLOOKUP OR INDEX

    or... an ordinary formula:

    =MIN(INDEX(((A$2:A$11=A14)*B$2:B$11)+((A$2:A$11<>A14)*1E+100),0))

  6. #6
    Registered User
    Join Date
    02-10-2017
    Location
    London
    MS-Off Ver
    2007
    Posts
    5

    Re: Help needed with VLOOKUP OR INDEX

    Thank you very much Glenn Kennedy, both formula works and solved the issue.

    Best Regards,

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Help needed with VLOOKUP OR INDEX

    You'rewelcome and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Vlookup or index match possibly needed
    By Noah101 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2017, 02:48 AM
  2. [SOLVED] Index Match / Vlookup / Other formula needed
    By rustywrightone in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-18-2017, 10:48 AM
  3. [SOLVED] Too Complicated for me (Vlookup/Index Help needed)
    By Ikaruza in forum Excel General
    Replies: 6
    Last Post: 04-13-2017, 12:57 AM
  4. Vlookup or index matching needed please help
    By SMITH.CRYSTAL in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-28-2015, 04:06 PM
  5. [SOLVED] Vlookup or Index Match Formula Needed
    By artiststevens in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2015, 01:09 AM
  6. [SOLVED] Index, Match, Vlookup, Hlookup help needed
    By Gemma_10 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2013, 01:05 PM
  7. Excel 2007 : Index,Vlookup function needed help
    By kalles in forum Excel General
    Replies: 1
    Last Post: 06-08-2012, 08:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1