Index /match

rmtaylor · Apr 27th, 2002, 03:38 AM

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 · Apr 27th, 2002, 03:56 AM

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 · May 18th, 2002, 11:45 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 27th, 2002, 03:38 AM	#1
rmtaylor Board Regular Join Date: Feb 2002 Location: Scotland Posts: 139	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 ]

Apr 27th, 2002, 03:56 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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

May 18th, 2002, 11:45 PM	#3
Corni Board Regular Join Date: Mar 2002 Location: Brampton Posts: 324	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.