+ Reply to Thread
Results 1 to 7 of 7

Find lowest value from criteia and show cell in same row to left 3 columns

  1. #1
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Find lowest value from criteia and show cell in same row to left 3 columns

    Hey guys&girls,

    I have a workbook with a "Capture" Sheet and a "price and supplier lookup" Sheet.

    In the "price and supplier lookup" Sheet, I have a dropdown list that shows the part names column "U" from the capture sheet and next to it a cell named "supplier" and another cell next to that named "price".

    1.jpg

    in the "price" cell, I have the following formula to find the lowest price
    Please Login or Register  to view this content.
    I need a formula to find the supplier name of the lowest price from Column J in the Capture sheet.

    Any help would be much appreciated.

    Thx.
    Last edited by ANDREAAS; 12-01-2018 at 08:35 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find lowest value from criteia and show cell in same row to left 3 columns

    Hi,

    Maybe

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If not upload the workbook so that we can see in context.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Find lowest value from criteia and show cell in same row to left 3 columns

    Hi Richard,

    Thank you for the response, unfortunately the workbook will not upload.....I've tried several times, so I hope the screenshots will help.

    below screenshot is from my "price & part lookup" sheet.

    1.jpg
    the cell called "Part" has a dropdown box under it that is connected to Column "U" of my "Capture" sheet. The cell called "Price" returns the lowest price from the criteria selected in the dropdown under "Part" and searches for this in my "Capture" sheet using this formula
    Please Login or Register  to view this content.
    .

    Below is my "Capture" sheet.
    2.jpg

    At present "Price" in my "price & part lookup" will show 201.25 and if you look at the "Capture" sheet, the corresponding supplier for that price is "TRANSWAREHOUSE", so I need the cell in my "price & part lookup" sheet between "Part" and "Price" under the cell called "supplier" to show "TRANSWAREHOUSE"

    I hope this helps?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find lowest value from criteia and show cell in same row to left 3 columns

    Assuming the Price returned of 284.05 is in say C2 then

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'm slightly confused since your original post says that the supplier names are in column J yet the Maintenance Register shown in your last post is column T.

    If this is not giving the answer yo want you cn upload a file by choosing Go Advanced and looking for the Manage Attachments underneath the posting area.

    The formula above is not isela since it uses whole column references which should usually be avoided since with some formulae and if there are large numbers of rows there will be an increase in time. In general always use specific ranges, e.g. MATCH(C2,X1:X100,....etc.

    Better still get into the habit of using dynamic range names if the the rows of the underlying data increases as you add new stuff. A dynamic range name in the formula automatilyl adjusts the range being searched and you dont have to worry about editing formulae.

  5. #5
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Find lowest value from criteia and show cell in same row to left 3 columns

    quite correct...it is column T, sorry for the confusion and thank you very much for the help so far. Will check your solution a bit later and get back to you.

    I used the procedure you described to load my file and the file is within the given size limit, but it keeps coming back as "file failed to upload"

  6. #6
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Find lowest value from criteia and show cell in same row to left 3 columns

    Thank you Richard, worked a charm!

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find lowest value from criteia and show cell in same row to left 3 columns

    My pleasure 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] Using VLOOKUP to find the lowest number in two columns
    By tom hatten in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-25-2018, 02:18 PM
  2. [SOLVED] i need to find the highest value in two columns, then present the cell contents two left
    By paulr0700282 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-18-2016, 01:55 PM
  3. Replies: 1
    Last Post: 07-25-2015, 07:23 AM
  4. Show lowest value from 3 columns in pivot table
    By ntrujillo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2015, 07:37 PM
  5. [SOLVED] Find lowest 5 values in a row, left to right
    By TallTex6 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-02-2015, 12:11 AM
  6. [SOLVED] 2 Columns - Find Data to Last Item Entry & Date to Left of Cell
    By mycon73 in forum Excel General
    Replies: 7
    Last Post: 08-17-2014, 08:00 PM

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