Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX and MATCH in one formula...
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: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=533063 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup? Index? match? formula | Excel Discussion (Misc queries) | |||
Index and Match Array formula | Excel Worksheet Functions | |||
index match formula | Excel Worksheet Functions | |||
How to add in an array formula if iisna index match | Excel Worksheet Functions | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) |