how to set up a vlookup table with 2 search terms?

W

WendyL

I am trying to set up a wages template for over 300 staffs, showing starting
and finishing times, hours worked, staff ID etc. I want to use 'Staff ID' and
'Date' as the search terms to help me calculate the hours work for staff. I
am unable to loacte any info online. I can only see 2-D vlookup and that
doesn't work because the date are in columns and not rows. Can anyone help
me?
 
D

David

WendyL said:
I am trying to set up a wages template for over 300 staffs, showing starting
and finishing times, hours worked, staff ID etc. I want to use 'Staff ID' and
'Date' as the search terms to help me calculate the hours work for staff. I
am unable to loacte any info online. I can only see 2-D vlookup and that
doesn't work because the date are in columns and not rows. Can anyone help
me?
Click to expand...

Sounds like AutoFilter is your best bet. If not then please try to explain
the problem clearly.
HTH
 
B

Bob Phillips

If you want to sum something, you can use

=SUMPRODUCT(--(staff_id=123),--(date_range=--"2005-03-01"), amount_range)

If you just want to get a value use

=INDEX(amount_range,MATCH("123"&=--"2005-03-01",TEXT(staff_id,0)&date_range,
0))

as an array formula
 
Top