Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Q
Hello.
I have two worksheets. Worksheet#1 has a unique ID field named ID which is populated, and some other fields, including a field named Formtype that is populated, a field named Hours that is blank. Worksheet#2 has a matching ID field and Formtype field, both populated, in addition to an hours field that is also populated with hours data. What I am trying to do is use the VLOOKUP function to match the ID AND the Formtype fields, and using these fields, find the corresponding hours value on worksheet #2 and use this value to populate the Hours value on Worksheet#1. Any ideas would be appreciated. Thnak you. Mark :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Q
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Q
One way
=INDEX(Sheet2!$C$2:$C$10,MATCH(A2&B2,Sheet2!$A$2:$ A$10&Sheet2!$B$2:$B$10,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom http://nwexcelsolutions.com "NWO" wrote in message ... Hello. I have two worksheets. Worksheet#1 has a unique ID field named ID which is populated, and some other fields, including a field named Formtype that is populated, a field named Hours that is blank. Worksheet#2 has a matching ID field and Formtype field, both populated, in addition to an hours field that is also populated with hours data. What I am trying to do is use the VLOOKUP function to match the ID AND the Formtype fields, and using these fields, find the corresponding hours value on worksheet #2 and use this value to populate the Hours value on Worksheet#1. Any ideas would be appreciated. Thnak you. Mark :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Q
Thank you.
I tried the formula, but I receive a different number (the lowest number in the range) than should be. Here is my formula, based on your suggestion: =INDEX(Hours_Data!$F$2:$F$6,MATCH(A5&E5,Hours_Data !$A$2:$A$6&Hours_Data!$E$2:$E$6,0)) (entered as an array formula) where Hours_Data is worksheet2 from my post. The columns on Hours_Data are as follows: User_ID = Column A Line_Number (same as Formtype) = Column E Hours = Column F I just choose rows 2 through 6 to start out as a test. So, I guess my question is, where did I go wrong? Please respond - thank you. Mark :) ----------------------- The corresponding columns on the Table_1 worksheet (worksheet1 on my post) are as foolws: User_ID = Column D Line_Number (same as Formtype) = Column E Hours = Column K " 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Q
Sorry, here si the formula that I meant to paste into my post:
=INDEX(Hours_Data!$F$2:$F$6,MATCH(0,((Hours_Data!$ A$2:$A$6=D2)*(Hours_Data!$E$2:$E$6=E2)))) Also, range Hours_Data!A2:A6 are all the same. Thanks, and sorry for the inconvenience. Still looking for a reply, though. Mark :) -------------- "NWO" wrote: Thank you. I tried the formula, but I receive a different number (the lowest number in the range) than should be. Here is my formula, based on your suggestion: =INDEX(Hours_Data!$F$2:$F$6,MATCH(A5&E5,Hours_Data !$A$2:$A$6&Hours_Data!$E$2:$E$6,0)) (entered as an array formula) where Hours_Data is worksheet2 from my post. The columns on Hours_Data are as follows: User_ID = Column A Line_Number (same as Formtype) = Column E Hours = Column F I just choose rows 2 through 6 to start out as a test. So, I guess my question is, where did I go wrong? Please respond - thank you. Mark :) ----------------------- The corresponding columns on the Table_1 worksheet (worksheet1 on my post) are as foolws: User_ID = Column D Line_Number (same as Formtype) = Column E Hours = Column K " 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |