+ Reply to Thread
Results 1 to 3 of 3

Using Small to find other columns/rows in the data

  1. #1
    Forum Contributor
    Join Date
    07-26-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2016 Office 365 ProPlus
    Posts
    822

    Using Small to find other columns/rows in the data

    I attached an example book because on "paper" is kind of hard to explain.

    But in the workbook you see on first tab some sort of table.
    In this case it is an overview of the lowest 3 outcomes in the data (with a SMALL formula).
    The data is on the 2nd tab.

    My problem lies within values that are in the data multiple times.
    In this case 180 is 2nd lowest and 3rd lowest.
    But 1 is in week 17 and the other in week 18.
    Also 1 week the name is Ceriel and the other week Rene.

    But now I am only getting twice week 17 (using a index match formula) en twice the name Ceriel (vlookup)
    Can anybody give the correct formulas to have more control on the name and the week in case a value occurs multiple times?
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Using Small to find other columns/rows in the data

    Using your posted workbook...
    this formula, copied down, returns the n-th lowest number
    Please Login or Register  to view this content.
    and these return the other data:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    07-26-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2016 Office 365 ProPlus
    Posts
    822

    Re: Using Small to find other columns/rows in the data

    Thanks Ron! Was quite the puzzle as I use ; instead of , so copy paste did not work.
    But going thru the formulas step by step I managed.

    ps: You have twice the same formula for Name and Week.
    But I figured out for Week the first part should be Sheet2!$A$2:$A$7 instead of Sheet2!$I$2:$I$7.

+ 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] Only small portion of rows and columns are visible
    By jmoffett in forum Excel General
    Replies: 4
    Last Post: 06-18-2014, 02:16 PM
  2. [SOLVED] Macro to find duplicates, then move data from rows into columns
    By givemepuppies in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-27-2014, 10:33 AM
  3. [SOLVED] VBA Find last row and column that contain data and delete all blank rows and blank columns
    By bcn1988 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-03-2012, 01:07 PM
  4. How to find duplicate rows where columns contain data of different types
    By olechkq in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2010, 01:18 PM
  5. Replies: 2
    Last Post: 03-27-2010, 12:43 PM
  6. Replies: 3
    Last Post: 03-19-2009, 09:11 AM
  7. Find data in columns, then place in rows
    By couriced in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2005, 12:44 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