HLookUp combined with a VLoopkUp...

G

Guest

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,...
 
G

Guest

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!!!
 
B

Biff

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.
Click to expand...

And this:
MonthNames = NamedRange= 'Values'!A2:D13 names of 12 months.
Click to expand...

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
 
Top