combine v and h lookups
 

I have the following roster counting the number of hours staff work on a
given day:

A B C D
1 Date 1 Date 2 Date 3
2 Name 1 8 6 7
3 Name 2 7 5 7
4 Name 3 6 3 4

In a report in a different workbook I need to have a formula to return the
contents of a cell based on both the name and the date: ie: lookup Name 3 and
date 3 and return the value of D4. I cannot use column / row numbers as the
positions change based on staffing changes.

How do I do this please?
--
scheduler

combine v and h lookups
 

=INDEX(A1:D4, MATCH(Name, A1:A4, 0), MATCH(Date, A1:D1, 0))

where Name and Date are cell references containing your criteria.



"scheduler" wrote:

I have the following roster counting the number of hours staff work on a
given day:

A B C D
1 Date 1 Date 2 Date 3
2 Name 1 8 6 7
3 Name 2 7 5 7
4 Name 3 6 3 4

In a report in a different workbook I need to have a formula to return the
contents of a cell based on both the name and the date: ie: lookup Name 3 and
date 3 and return the value of D4. I cannot use column / row numbers as the
positions change based on staffing changes.

How do I do this please?
--
scheduler