Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Index /match

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Scotland
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Help Please
    Match or 1ndex/ Match
    Can anyone help me with this problem. The required answer would be the value from the cell in column A.
    I am attempting to search through a list and match the number of times three (company, address, & result) arguments are matched in rows and show the result as the cells value In cells in column F. Example below

    {"A","B","C","D","E","F";0,"Company"," Ref","Address","Result","Matched inspections";0,"BT","P/02/99","THE AVENUE","PASS","NA";1,"SP","P/02/97","MAIN ST","FAIL","NA";2,"sw","P/02/98","KING ST","FAIL","NA";3,"SP","P/02/99","THE AVENUE","PASS","NA";4,"SP","P/02/11","MAIN ST","FAIL",2;5,"TR","P/02/12","BOW ST","FAIL","NA";6,"TR","P/02/12","BOW ST","FAIL",6}

    Cheers
    Thanks for any replies
    Robert Scotland

    Ps if the arguments were matched more than once could all cell values be shown as well.


    [ This Message was edited by: rmtaylor on 2002-04-27 03:31 ]

    [ This Message was edited by: rmtaylor on 2002-04-27 03:59 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,024
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    Robert,

    The sample you posted is difficult to interpret. Care to repost the sample by using the following procedure?

    Select an unused cell, type =, select the sample data (10 rows including labels/column headings), hit F9, copy (Edit|Copy) what you see, and paste it in the follow up.

    Aladin

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Enter following formula in cell F2:

    =IF(SUMPRODUCT(($B$2:B2=B2)*($D$2:D2=D2)*($E$2:E2=E2))=1,"NA",SUMPRODUCT(($B$2:B2=B2)*($D$2:D2=D2)*($E$2:E2=E2)))

    and then copy the formula down the column for all records.

Some videos you may like

User Tag List

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
  •