+ Reply to Thread
Results 1 to 7 of 7

Lookup formula to find lowest value with variable lookup criteria

  1. #1
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

    Lookup formula to find lowest value with variable lookup criteria

    I have a table with product names as column headers and vendors as row headers. The body of the table is populated with product costs for each vendor. I want to create a second table with products as row headers and price leader / Price as column headers. The idea would be for the formula to use the row header from the second table to identify the column it should look at from the first table and find the cheapest price (and the vendor that sells at that price).

    I've included a copy of my data for reference purposes.

    Thanks in advance

    Bruce

    P1 Excel raw data file.xlsx

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,881

    Re: Lookup formula to find lowest value with variable lookup criteria

    What if there are more than one vendor with same smallest price?

  3. #3
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Lookup formula to find lowest value with variable lookup criteria

    ooh, good question. I hadn't thought of that. It seems like I would need to create a second vendor column. Unless you have any other suggestion?

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,881

    Re: Lookup formula to find lowest value with variable lookup criteria

    Yes. Products / Price / Vendor 1 / Vendor 2 / Vendor 3 / ......

  5. #5
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Lookup formula to find lowest value with variable lookup criteria

    Ok, that would work for what I'm trying to do. Do you know if it's possible to set a formula up that would do what I described?

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,881

    Re: Lookup formula to find lowest value with variable lookup criteria

    See attachment.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Lookup formula to find lowest value with variable lookup criteria

    amazing, that is fantastic. Thank you very much.

+ 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] using a lookup formula to find all rows that match same criteria
    By nicki_rae22 in forum Excel General
    Replies: 3
    Last Post: 12-09-2014, 10:38 AM
  2. [SOLVED] A lokup formula where the lookup value is less than the lowest value in the range
    By marinemonk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2012, 03:44 PM
  3. Search lookup array to find lookup value contained within text string
    By Cookstein2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-02-2011, 09:42 AM
  4. Replies: 3
    Last Post: 07-11-2009, 02:58 PM
  5. How do I use lookup to find data based on 3 criteria?
    By jerickson in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-03-2008, 09:26 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