Share this content
4

Excel - Looking up values using two criteria

I have a spreadsheet that shows in separate columns: Date of job, client ref, job done by (staff)

Didn't find your answer?

Is there a formula that I can use in a separate sheet that will look up the client ref and return the name of the staff member who carried out the last job?

 

Replies (4)

Please login or register to join the discussion.

avatar
By johnhemming
27th Jul 2019 06:45

Although you could use LOOKUP to get a staff member from a client reference where you have multiple jobs for the same client it would not pick up the last job. It would probably pick up the first. (You would have to find this out by testing it and it might be a bit random).

If the date of the job is stored as a date so that MATCH will find the greatest date where the client ref is the required value then using INDEX given the result of MATCH might give you what you want.

I think you would probably have to have an extra column in the source worksheet which takes the date and copies it if the Client Code is the client code that you are looking for which enables you to simply look for the largest value in that column with match (as it would be zero if not the correct client code). I am not sure match with multiple conditions will give you what you want.

It depends then on how complicated any other things are that you want to do because you might find it worth getting into excel basic where this sort of thing can be quite straightforward.

If using Excel formulae, you would have to enter this sort of thing (Match) as an array formula
https://exceljet.net/glossary/array-formula

Thanks (1)
avatar
By Try_to_help
27th Jul 2019 06:46

Assume that the client refs are in cells A1:A10 and the corresponding staff names are in cells B1:B10 and C1 contains the client ref that you want to look up.

Then the following formula should do what you want:

=LOOKUP(2,1/($A$1:$A$10=$C$1),$B$1:$B$10)

Thanks (2)
Replying to Try_to_help:
avatar
By ian339
17th Aug 2019 04:09

Would you kindly explain how the formula works, please? I thought I understood Excel quite well, until now! The lookup_value of 2 in particular escapes me.

Thanks (0)
avatar
By MC1
18th Aug 2019 09:05

Are you wanting to list the last job for all clients and keep this list updated eg for new clients as well as new jobs done?

Or do you just need to query one client on an ad hoc basis?

Thanks (0)
Share this content