Index /match

[rmtaylor]

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

 

[Aladin Akyurek]

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

 

[Corni]

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.