I have an array formula in an excel worksheet which compares values o
one sheet to values on another and returns matching data the record.

This formula is as follows:

=INDEX(Subjects!$E$31:$E$511, MATCH($A14 & $M14 & $J14,
Subjects!$B$31:$B$511 & Subjects!$K$31:$K$511 & Subjects!$I$31:$I513
0))

As you can see it is matching 3 columns on the current sheet to
columns on the subjects sheet and returning the value in column E o
the subject sheet for the matching record. This all works fine.

But I would like to move the formula out of the spreadsheet and int
some VBA code, so it will work on the press of a button on th
spreadsheet instead.

The problem is, I can get this type of formula to work in VBA whe
there is a single match e.g.

ActiveCell.Offset(0, 4)
Application.WorksheetFunction.Index(Worksheets("Su bjects").Range("B31:N513")
Application.WorksheetFunction.Match(Comsubno.Value
Worksheets("Subjects").Range("B31:B513"), 1), 13)

But I cannot get multiple matches to work e.g.

ActiveCell.Offset(0, 11)
Application.WorksheetFunction.Index(Worksheets("Su bjects").Range("D31:D511")
Application.WorksheetFunction.Match(Comsubno.Value & Comlabproc.Value
Comtimept.Value, Worksheets("Subjects").Range("B31:B511")
Worksheets("Subjects").Range("K31:K511")
Worksheets("Subjects").Range("I31:I511"), 0))

Any Ideas anyone ????

--
Message posted from http://www.ExcelForum.com