Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLookUp combined with a VLoopkUp...
The the Guru's:
What I need is to create a cross section using a sort of HLookUp and VLookUp function. HLookUp to find a Employee name on a specific row in column "A", then use the VLookUp to pick up specific data for the employee four (5) rows columns F - Q down at the intersection point! Is that possible? If it is, how can I get it done? Column "A1:A45" has Employee names every 5th row starting at row A5. Now across heading of columns "F" through "Q" are month of year names. What I need is to select a employee name on row "A5" and have all the data for that employee show up for column "M5, M6, M7, M8 and M9" = August data for this employee. If I select an employee name in row A15, I want to see the data for this employee listed in column N15, N16, N17, N18,N19 = September data for this employee. See sample data below, I hope it fits: A J K L M N May June Jul Aug Sept Oct Javier Garza 21900.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 0.00 0.00 876.00 AZV = Employee (1.6%) 0.00 0.00 350.40 Totals Juan Mattos 1250.00 1350.00 1400.00 1400.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 50.00 54.00 56.00 56.00 AZV = Employee (1.6%) 20.00 21.60 22.40 22.40 Totals If there are questions, please, post back! Thanks,... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLookUp combined with a VLoopkUp...
Here's a sample file.
Sample_lookup.xls 16kb http://cjoint.com/?jow0ViYB1R Biff "Jay" wrote in message ... The the Guru's: What I need is to create a cross section using a sort of HLookUp and VLookUp function. HLookUp to find a Employee name on a specific row in column "A", then use the VLookUp to pick up specific data for the employee four (5) rows columns F - Q down at the intersection point! Is that possible? If it is, how can I get it done? Column "A1:A45" has Employee names every 5th row starting at row A5. Now across heading of columns "F" through "Q" are month of year names. What I need is to select a employee name on row "A5" and have all the data for that employee show up for column "M5, M6, M7, M8 and M9" = August data for this employee. If I select an employee name in row A15, I want to see the data for this employee listed in column N15, N16, N17, N18,N19 = September data for this employee. See sample data below, I hope it fits: A J K L M N May June Jul Aug Sept Oct Javier Garza 21900.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 0.00 0.00 876.00 AZV = Employee (1.6%) 0.00 0.00 350.40 Totals Juan Mattos 1250.00 1350.00 1400.00 1400.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 50.00 54.00 56.00 56.00 AZV = Employee (1.6%) 20.00 21.60 22.40 22.40 Totals If there are questions, please, post back! Thanks,... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLookUp combined with a VLoopkUp...
Hey Biff:
I used your sample of the "Index"-"Match" function. On your sample file when the "Employee Name" is selected and the "Month Name" is set, I got ALL the correct data each time. However, when I applied the same to my file I keep getting bad data! Example, when I pick the first Employee Name, I get the correct data for him/her! When I select the second employee name, third, fourth and fifth names the data only drops down by one record/row from the first selection! How can I correct this? Here is a sample of the line item data selection: =INDEX(EmployeeDataVals,MATCH(I$4,EmployeeNames,0) +ROWS($1:1)-1,MATCH(K$4,MonthNames,0) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:2)-1,MATCH(K$4,MonthNames,0)) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:3)-1,MATCH(K$4,MonthNames,0)) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:4)-1,MATCH(K$4,MonthNames,0)) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:5)-1,MATCH(K$4,MonthNames,0)) ======== ======== Possible issues? Compare these data sources below to those above? ======== EmployeeDataVals = NamedRange= 'Emp Data'!F5:Q49 data values from Jan to Dec EmployeeNames = NamedRange= 'Values'!D2:D10 names of employees. MonthNames = NamedRange= 'Values'!A2:D13 names of 12 months. Is something falling out? I feel that it is! HELP!!! "Biff" wrote: Here's a sample file. Sample_lookup.xls 16kb http://cjoint.com/?jow0ViYB1R Biff "Jay" wrote in message ... The the Guru's: What I need is to create a cross section using a sort of HLookUp and VLookUp function. HLookUp to find a Employee name on a specific row in column "A", then use the VLookUp to pick up specific data for the employee four (5) rows columns F - Q down at the intersection point! Is that possible? If it is, how can I get it done? Column "A1:A45" has Employee names every 5th row starting at row A5. Now across heading of columns "F" through "Q" are month of year names. What I need is to select a employee name on row "A5" and have all the data for that employee show up for column "M5, M6, M7, M8 and M9" = August data for this employee. If I select an employee name in row A15, I want to see the data for this employee listed in column N15, N16, N17, N18,N19 = September data for this employee. See sample data below, I hope it fits: A J K L M N May June Jul Aug Sept Oct Javier Garza 21900.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 0.00 0.00 876.00 AZV = Employee (1.6%) 0.00 0.00 350.40 Totals Juan Mattos 1250.00 1350.00 1400.00 1400.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 50.00 54.00 56.00 56.00 AZV = Employee (1.6%) 20.00 21.60 22.40 22.40 Totals If there are questions, please, post back! Thanks,... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLookUp combined with a VLoopkUp...
This looks suspicious to me. In fact, I'm pretty sure that's where things
are getting messed up!!! EmployeeNames = NamedRange= 'Values'!D2:D10 names of employees. And this: MonthNames = NamedRange= 'Values'!A2:D13 names of 12 months. Did you mean A2:A13 ? You want to send me a copy of your file and I'll do it for you? If so, my address is: xl can help at comcast period net Remove "can" and change the obvious. Biff "Jay" wrote in message ... Hey Biff: I used your sample of the "Index"-"Match" function. On your sample file when the "Employee Name" is selected and the "Month Name" is set, I got ALL the correct data each time. However, when I applied the same to my file I keep getting bad data! Example, when I pick the first Employee Name, I get the correct data for him/her! When I select the second employee name, third, fourth and fifth names the data only drops down by one record/row from the first selection! How can I correct this? Here is a sample of the line item data selection: =INDEX(EmployeeDataVals,MATCH(I$4,EmployeeNames,0) +ROWS($1:1)-1,MATCH(K$4,MonthNames,0) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:2)-1,MATCH(K$4,MonthNames,0)) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:3)-1,MATCH(K$4,MonthNames,0)) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:4)-1,MATCH(K$4,MonthNames,0)) =INDEX(EmployeeDataVals,MATCH($I$4,EmployeeNames,0 )+ROWS($1:5)-1,MATCH(K$4,MonthNames,0)) ======== ======== Possible issues? Compare these data sources below to those above? ======== EmployeeDataVals = NamedRange= 'Emp Data'!F5:Q49 data values from Jan to Dec EmployeeNames = NamedRange= 'Values'!D2:D10 names of employees. MonthNames = NamedRange= 'Values'!A2:D13 names of 12 months. Is something falling out? I feel that it is! HELP!!! "Biff" wrote: Here's a sample file. Sample_lookup.xls 16kb http://cjoint.com/?jow0ViYB1R Biff "Jay" wrote in message ... The the Guru's: What I need is to create a cross section using a sort of HLookUp and VLookUp function. HLookUp to find a Employee name on a specific row in column "A", then use the VLookUp to pick up specific data for the employee four (5) rows columns F - Q down at the intersection point! Is that possible? If it is, how can I get it done? Column "A1:A45" has Employee names every 5th row starting at row A5. Now across heading of columns "F" through "Q" are month of year names. What I need is to select a employee name on row "A5" and have all the data for that employee show up for column "M5, M6, M7, M8 and M9" = August data for this employee. If I select an employee name in row A15, I want to see the data for this employee listed in column N15, N16, N17, N18,N19 = September data for this employee. See sample data below, I hope it fits: A J K L M N May June Jul Aug Sept Oct Javier Garza 21900.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 0.00 0.00 876.00 AZV = Employee (1.6%) 0.00 0.00 350.40 Totals Juan Mattos 1250.00 1350.00 1400.00 1400.00 Income Tax / Loon Belaasting AOV / AWW = Employee(4%) 50.00 54.00 56.00 56.00 AZV = Employee (1.6%) 20.00 21.60 22.40 22.40 Totals If there are questions, please, post back! Thanks,... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup and hlookup | Excel Worksheet Functions | |||
combined HLOOKUP (urgent for a friend) | Excel Worksheet Functions | |||
Problem with HLookup - Sometimes works, sometimes blank. | Excel Discussion (Misc queries) | |||
Index / Hlookup | Excel Worksheet Functions | |||
Need help with HLOOKUP and MATCH functions | Excel Worksheet Functions |