+ Reply to Thread
Results 1 to 5 of 5

Index/Match - compare two ranges though

  1. #1
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Index/Match - compare two ranges though

    My objective is to find the date/time in A column when cell E is less than D or D is less than E, based upon the If criteria.

    I tried using:
    INDEX(WrkStName!$A$3:$A$100000, MATCH(1,(RANGE1 < RANGE 2)
    (The actual formula is in the attached sample. For some reason, it would not let me post here, calling it HTML code (even when using [Code]))

    But my formula did not work. How should I do it correctly?

    I thank you for your guidance in advance. Example sheet attached (the ranges go for 100,000 rows, but reduced for simplicity).
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Index/Match - compare two ranges though

    Please try

    =INDEX(WrkStName!$A$3:$A$100000, MATCH(true,RANGE1 < RANGE 2,))


    =INDEX(USDJPYMAs!$A$3:$A$100000,MATCH(TRUE,INDEX(USDJPYMAs!$E$3:$E$100000<USDJPYMAs!$D$3:$D$100000,),0))

  3. #3
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Re: Index/Match - compare two ranges though

    Wow, thank you. I will give that a try.

    Another related question. How would I do that with Match using multiple criteria for a different formula? Do I need to do another Match inside this Match?

    Right now I have
    HTML Code: 

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Index/Match - compare two ranges though

    No, but Exact match is need
    =INDEX(WrkStName!$A$3:$A$100000,MATCH(1,(Criteria1) * (Criteria2) * (WrkStName!$E$3:$E$100000 < WrkStName!$D$3:$D$100000),0)

  5. #5
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Re: Index/Match - compare two ranges though

    Okay, Actually, I did have exact match setup. So then you confirm that doing it this way with exact match works?

    Again, thanks for all your help!

+ 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] Compare two ranges and update one to match another
    By TPDave in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-29-2018, 08:06 AM
  2. [SOLVED] Compare ranges on 2 sheets, exit VBA if they don't match
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2018, 10:03 PM
  3. Index match and (Compare)
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2017, 09:20 AM
  4. Replies: 4
    Last Post: 04-28-2017, 03:24 AM
  5. [SOLVED] Compare ANY cells that match in two ranges (UDF)
    By Lapper in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 05-05-2016, 05:35 AM
  6. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  7. Compare range with previous ranges,display elapsed row match
    By sans in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-19-2013, 12:08 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