Results 1 to 5 of 5

Thread: Issue with MATCH
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Issue with MATCH

    Hey all,

    FormR kindly helped with combining two formulas together:

    =IF(AND(INDEX(T:T,MATCH(E5,AF:$AF,0))=G5,INDEX(BJ:BJ,MATCH(E5,AF:$AF,0))=H5),"Yes","No")

    This formula is replicated in Rows until 1500 i.e.

    =IF(AND(INDEX(T:T,MATCH(E6,AF:$AF,0))=G6,INDEX(BJ:BJ,MATCH(E6,AF:$AF,0))=H6),"Yes","No")

    etc etc

    The issue that I'm now facing is that on occasions the data inputted in Column E and AF may be the same in multiple cells i.e. Smith, John. As a result it's often incorrectly returning a 'No' as it's picking up the first Smith, John in AF. What I want it do if possible is to continue searching for the exact match (T:T & G5, BJ:BJ & H5 etc) and return a 'No' then if it's not found.

    Hopefully that makes sense

    Can provide more info if needed.

    Cheers for any help people can provide!

    Matt

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,984
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Issue with MATCH

    are you certain there are 0 or 1 perfect matches ?

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,984
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Issue with MATCH

    row 4
    bob smith 9 bob jones 14 row 5
    tom jones 7
    b jones 5
    rob jones 12
    bob jones 14 14 obtained by
    bill green 16
    =OFFSET($A$4,MATCH(E5,$A$5:$A$100,0),1)

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,183
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Issue with MATCH

    Use this:

    sheet1

     AEFGHTAFBJ
    1        
    4 NameFormulaData1Data2First searchNamesSecond Search
    5 John SmithYesnewoldwithoutJohn Smithnew
    6 CarolNoSwitchwhitewithoutJohn Smithnew
    7 SueYesislabonitanewJohn Smithold
    8     SwitchCarolYellow
    9     SwitchCarolPink
    10     SwitchCarolblue
    11     islaSuecan
    12     islaSuebonita
    13     islaSuedessert

    Formula
    CellFormula
    F5=IF(SUMPRODUCT(($AF$5:AF100=E5)*($T$5:T100=G5)*($BJ$5:BJ100=H5))>0, "Yes","No")
    F6=IF(SUMPRODUCT(($AF$5:AF101=E6)*($T$5:T101=G6)*($BJ$5:BJ101=H6))>0, "Yes","No")
    F7=IF(SUMPRODUCT(($AF$5:AF102=E7)*($T$5:T102=G7)*($BJ$5:BJ102=H7))>0, "Yes","No")


    Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
    Last edited by DanteAmor; Mar 11th, 2019 at 01:41 PM.
    Regards Dante Amor

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Issue with MATCH

    Quote Originally Posted by shakeregg View Post

    […]

    Can provide more info if needed.

    […]
    Try to provide a small sample along with the desired output.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •