Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Lookup Cheapest (Excel XP)

    Hi,

    How can I get VLookup to pull out the cheapest price of a particulat product supplied by 3 or 4 different suppliers. Vlookup will find a match on a product code, but there may be 4 of that code. Once found, I need the cheapest product price to be returned for that product code.

    Thanks for any assistance.
    See the attachment for sample.
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Cheapest (Excel XP)

    You don't use VLookup for this. Use this array formula (confirm with Ctrl+Shift+Enter):

    =MIN(IF(Sheet2!$A$2:$A$5=A2,Sheet2!$D$2:$D$5))

    Fill down as far as needed.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup Cheapest (Excel XP)

    I'll give it a try. TX Hans!
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup Cheapest (Excel XP)

    TX Hans,

    The formula works well. (See the datafile attached).
    I also need the supplier code collected from sheet2. Since this is not a number I figured that we can use the min function you sent me to collect the cheapest price and then on that same line offset to collect the supplier code for that record. Any ideas as to how to offset to collect the supplier code into sheet 1?
    Tx
    Attached Files Attached Files
    Regards,
    Rudi

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Lookup Cheapest (Excel XP)

    If you will only ever have one match per product, you can use:
    <code>=INDEX(Sheet2!$B$2:$B$5,SUMPRODUCT((Sheet2!$ A$2:$A$5=Sheet1!A2)*(Sheet2!$D$2:$D$5=Sheet1!D2)*( ROW(Sheet2!$D$2:$D$5)-1)))</code>


    with references adjusted as necessary for your real data.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Lookup Cheapest (Excel XP)

    Probably safer to use this array formula:
    <code>=INDEX(Sheet2!$B$2:$B$5,MIN(IF((Sheet2!$A$2: $A$5=Sheet1!A2)*(Sheet2!$D$2:$D$5=Sheet1!D2),(ROW( Sheet2!$D$2:$D$5)-1),"")))</code>

    and the full version with error checking in case of no matches:
    =IF(COUNTIF(Sheet2!$A$2:$A$5,Sheet1!A2)=0,"",INDEX (Sheet2!$B$2:$B$5,MIN(IF((Sheet2!$A$2:$A$5=Sheet1! A2)*(Sheet2!$D$2:$D$5=Sheet1!D2),(ROW(Sheet2!$D$2: $D$5)-1),""))))
    which also needs to be array-entered.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup Cheapest (Excel XP)

    Wow Rory...
    I wont even go there to ask how you came out to such a solutuion!

    Many tx to you and Hans.

    Cheers
    Regards,
    Rudi

Posting Permissions

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