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
--------------
gt; quot; wrote:
gt;
gt; gt; You can do this fairly easily with index/match;
gt; gt;
gt; gt; INDEX(Sheet1!A1:A10,MATCH(1,((Sheet1!B1:B10=idNum) *(Sheet1!C1:C10=formType)))
gt; gt;
gt; gt; column A contains the value you want to return from the other sheet,
gt; gt; column B contains the ID numbers, and C contains the form type. of
gt; gt; course you'll change quot;idNumquot; and quot;formTypequot; to whatever cell you have
gt; gt; to to identify that person and form.
gt; gt;
gt; gt; For your specific problem, you would replace sheet1! in the formula
gt; gt; with worksheet2! and you would put the formula in worksheet1! with
gt; gt; idNum and formType pointing towards the cells on that row.
gt; gt;
gt; gt;
You need a third argument for the MATCH function, i.e.
=INDEX(Hours_Data!$F$2:$F$6,MATCH(1,((Hours_Data!$ A$2:$A$6=D2)*(Hours_Data!$E$2:$E$6=E2)),*0*))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=533063
- Jul 16 Mon 2007 20:38
INDEX and MATCH in one formula...
close
全站熱搜
留言列表
發表留言