ExcelBanter - INDEX and MATCH in one formula...

Hello.

What I am trying to do is capture hours data from Hours_Data worksheet
(column F), based on a match of the User_ID and Line_Number field values,
whcih are listed on both worksheets.

The columns on the Hours_Data worksheet are as follows:

User_ID = Column A
Line_Number = Column E
Hours = Column F

The columns on Table1 worksheet are as follows:

User_ID = Column D
Line_Number = Column E
Hours = Column K

Here is a formula that I am using based on input form another user yesterday
(see below string of e-mails):

=INDEX(Hours_Data!$F$2:$F$6,MATCH(1,((Hours_Data!$ A$2:$A$6=D2)*(Hours_Data!$E$2:$E$6=E2))))

(entered as an array formula)

The problem is that although I do receive values, they are out of sequence.
For example, as test data, if the hours values are 6, 7, 8, 9, and 10 in
column F (rows 2 through 6) of the Hours_Data worksheet, when the above
formula is entered and copied in cells K2, K3, K4, K5, and K6 on the Table1
worksheet, I receive the values 10, 10, 7, 8, 9!

What am I doing wrong. Does the column order matter on either worksheet? I
tried making Match_Type chnages (i.e. 1,0, and -1), I still didn;t receive
the correct values. Note that the values in the USER ID column are the same.
I am only doing a test as the data has lots of repeating USER IDs - each
representing a different form type - I don't know if this causes a problem.

Any suggestions would be appreciated.

Mark :)
--------------

" wrote:

You can do this fairly easily with index/match;

INDEX(Sheet1!A1:A10,MATCH(1,((Sheet1!B1:B10=idNum) *(Sheet1!C1:C10=formType)))

column A contains the value you want to return from the other sheet,
column B contains the ID numbers, and C contains the form type. of
course you'll change "idNum" and "formType" to whatever cell you have
to to identify that person and form.

For your specific problem, you would replace sheet1! in the formula
with worksheet2! and you would put the formula in worksheet1! with
idNum and formType pointing towards the cells on that row.