+ Reply to Thread
Results 1 to 6 of 6

index match with large and bigger than

  1. #1
    Registered User
    Join Date
    03-22-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    40

    index match with large and bigger than

    Hi guys, how do I use Index match where I want to use LARGE and ">20" where the two values are in different columns?

    Capture2.PNG

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: index match with large and bigger than

    into I3:

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

    accept with Ctrl+Shift+Enter and drag it down.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: index match with large and bigger than

    Try this. In E2 then drag down.

    =IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($B$2:$B$6)/($B$2:$B$6> 20),ROWS($E$2:$E2))-ROW($B$2)+1),"")
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    03-22-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    40

    Re: index match with large and bigger than

    Quote Originally Posted by KOKOSEK View Post
    into I3:

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

    accept with Ctrl+Shift+Enter and drag it down.
    Thanks KOKOSEK, this seems to do the trick! Just wondering how the ROW()-2 works...

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: index match with large and bigger than

    You welcome.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  6. #6
    Registered User
    Join Date
    04-29-2014
    Location
    ......
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: index match with large and bigger than

    i ask one question.If i want >=20,<=30,the answer should be within i3 one cell with commos ,descending order and numbers not repeated,without#num.IE 6,2.Suppose both instead of value 2 is 6.i need one time 6.what formula need achieve this answer?.same cell with in multiple anwers with commos,without repeat the numbers.
    Last edited by pok; 06-19-2019 at 10:34 AM.

+ 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. Index / Match / Large / If help
    By jadiepants in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-16-2018, 06:08 PM
  2. [SOLVED] Large Data - Index Match Match with another function?
    By d7882 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-12-2017, 03:51 PM
  3. Index Large Match
    By normanbox in forum Excel General
    Replies: 3
    Last Post: 04-11-2017, 04:18 PM
  4. INDEX, MATCH and LARGE
    By ridebikes in forum Excel General
    Replies: 1
    Last Post: 10-24-2011, 04:03 PM
  5. Index, Match, and Large
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2006, 06:10 PM
  6. [SOLVED] Large Index Match Lookup
    By Qaspec in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  7. Large Index Match Lookup
    By Qaspec in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1