+ Reply to Thread
Results 1 to 5 of 5

Match in range of 2 less

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    uk
    MS-Off Ver
    Excel 2013
    Posts
    99

    Match in range of 2 less

    Hi

    I have had great help on my project with formulas of which i am making varients of some formulas given for different criteria. I am having trouble with part of a match formula which i want to change slightly: =IF(AND(EXACT($O3,$X3) is the part i wish to change so that Cell O3 can be in range by 2 less and still be a match i.e O3=4 & X3=4 is a match but also O3=2 & X3=4 is a match But O3=5 & X3=4 is NOT match


    Here is the full formula so you know not to effect anything else:

    =IF(AND(EXACT($O3,$X3),EXACT(TRIM(SUBSTITUTE(K3,MAX(IFERROR(--MID(K3,1,ROW(INDIRECT("1:"&LEN(K3)))),"")),"")),TRIM(SUBSTITUTE(V3,MAX(IFERROR(--MID(V3,1,ROW(INDIRECT("1:"&LEN(V3)))),"")),"")))),IF(AND(N3<=3,ISNUMBER(MATCH(TRUE,MAX(IFERROR(--MID(K3,1,ROW(INDIRECT("1:"&LEN(K3)))),""))=MAX(IFERROR(--MID(V3,1,ROW(INDIRECT("1:"&LEN(V3)))),""))+{0,1,2,3},0))),5,""),"")


    Thanks

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Match in range of 2 less

    If you switch the EXACT($O3,$X3) part for AND($O3<=$x3,$O3>=$X3-2) does that do what you need?

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: Match in range of 2 less

    EXACT function is designed in texts (strings) comparision. If you want to compare numbers you can just use simple operators < > = etc.

    so to extend EXACT($O3,$X3) on range of 2 and less you could write:

    AND($O3<=$X3,$O3>=$X3-2)

    as for the rest of the formula - not seeing sample data it is not so easy to assess what changes would be necessary - it looks that you compare strings there, so EXACT is proper function to use there)
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    01-16-2014
    Location
    uk
    MS-Off Ver
    Excel 2013
    Posts
    99

    Re: Match in range of 2 less

    Worked perfectly like this, Thank you very much

    =IF(AND(AND($O3<=$x3,$O3>=$X3-2)

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: Match in range of 2 less

    As it is AND operation you can also use only one, not the and(and(...),

    Please Login or Register  to view this content.

+ 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] Index Match Match for weekly range
    By onemoremile in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2014, 04:46 PM
  2. [SOLVED] reference date time range, return the value into all cell that match datetime range
    By Jarvco13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2013, 10:39 PM
  3. [SOLVED] Dynamic Name Range using Offset&Match with Match based off a different column
    By mdlpjr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2013, 06:33 PM
  4. match a cell in a range from a master range & then mark it in some way as matched
    By andrewsparrow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2013, 03:38 PM
  5. Match values in a range with another range and copy the adjacent column
    By xelmac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2012, 01:42 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