Lookup within a lookup

[dhatul]

I have a number of transactions on various dates for a number of employees. I want to find out the amount of the transaction on a particular date of a particular employee. What I tried was as under -

1. Set filter on the employee code for the given employee
2. Now I vlookup the excel sheet for the given date.

I had presumed that the vlookup will this way work only on the filtered data, but obviously that is not the case. How can I make vlookup work only on the filtered data?

Can someone suggest the best method for achieving the above objective?

[pklimchuk]

what about a Pivot Table

[dhatul]

what about a Pivot Table

I wanted to restrict myself to the original data itself, without writing the filtered data elsewhere (as in a pivottable).

Say, can I sort the data on employee no.+transaction date. Filter on employee no. Then find out the row no. of the first filtered record and the last filtererd record (they will be contiguous, since I sorted on employee no.) and then restrict the vlookup row between the first and the last filtered record rows?

How's that? But how to find out the row nos.? Any simple way?

[dhatul]

Alternatively, cansomeone suggest a way to restrict vlookup to visible rows only (after filtering).

[DonkeyOte]

I have a number of transactions on various dates for a number of employees. I want to find out the amount of the transaction on a particular date of a particular employee...

Can someone suggest the best method for achieving the above objective?

If the combination of date & employee is unique (or if not unique you wish to aggregate) then given use of XL2007 and the fact the resulting value is numeric use SUMIFS - no need to filter etc.

[dhatul]

If the combination of date & employee is unique (or if not unique you wish to aggregate) then given use of XL2007 and the fact the resulting value is numeric use SUMIFS - no need to filter etc.

Wonderful. I could have never imagined that an unrelated function can be so powerful and effective.

Thanks a lot.