+ Reply to Thread
Results 1 to 6 of 6

VLOOKUP Showing MIN VALUE IF MATCH

  1. 02-16-2012, 05:04 PM #1
    crizz82
    crizz82 is offline
    Registered User
    Join Date
    02-16-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    3

    VLOOKUP Showing MIN VALUE IF MATCH

    Hi Guys,

    Really enjoyed the information found in the different threads here, very helpful.
    I still have one issue that I really need to solve.

    Scenario:
    I have a bunch of prospects that I would like to keep track of.
    They are interested in purchasing different quantities of my products IF I can match their target price.

    What I would like to do is a targetlist sheet showing the prospects target price, potential volume and also show me which prospect that has the lowest target price. The last part is my problem, I've been able to sort out the total volume and lowest target price so far.

    I'm more than happy to reply to all your questions and I've added an example for help.

    BR
    Christian
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-16-2012, 05:14 PM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: VLOOKUP Showing MIN VALUE IF MATCH

    Your Target Price formula is incorrect
    Take a look at H3
    Now look at H4, your range is now A3:A11 which is outside the table range
    it should be

    =MIN(IF(A$2:A$10=F3,C$2:C$10))

    Note the $ signs
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 02-16-2012, 05:18 PM #3
    crizz82
    crizz82 is offline
    Registered User
    Join Date
    02-16-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLOOKUP Showing MIN VALUE IF MATCH

    Great, thanks!
    Any success on the other part of the issue?

    //Christian
    Reply With QuoteRegister To Reply
  4. 02-16-2012, 05:21 PM #4
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: VLOOKUP Showing MIN VALUE IF MATCH

    Try this

    in G3

    =INDEX(B$2:B$10,MATCH(MIN(IF(A$2:A$10=F3,C$2:C$10)),C$2:C$10,0),1)

    (Note Array Formula)
    and copy down to G6

    Am just wondering what happens if more than one customer has the lowest price, this formula will just return the first lowest price customer it finds
    Reply With QuoteRegister To Reply
  5. 02-16-2012, 05:37 PM #5
    crizz82
    crizz82 is offline
    Registered User
    Join Date
    02-16-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLOOKUP Showing MIN VALUE IF MATCH

    Worked like a charm

    Would you mind also guide me to remove the #N/A if no customer for the product?

    //Christian
    Reply With QuoteRegister To Reply
  6. 02-16-2012, 05:57 PM #6
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: VLOOKUP Showing MIN VALUE IF MATCH

    Usual solution is

    =IF(ISNA(formula),"",formula)

    You'll have to insert the formula twice in the above whereever you need to use the formula
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1