Lookup help

I have a list of data that contains employee code, date, pay code and pay qty.
I want to return a pay code for a particular employee on a particular date, but only if its a leave code (ie ANN for annual).
An employee can have more than one pay code for a date, or none.
How do I get excel to select only the particular type of code that I want to return? There are multiple leave codes to choose from.

Thanks!

I'd probably just use a hash column to generate a unique lookup code, then hit it with a VLOOKUP.

Example attached.

Thanks for your help, but there are multiple leave codes that need to be returned - ie sick, timeoff.
so while the date and employee are constants in the formula, the leave code is a variable. I also want to return the code, not the qty. can this be done without creating a lookup for each code ?
see updated example which includes a sick line.

Can you draft up a sample of what a more complete table would be, as well as range that shows what you're trying to get? Show us what you want to input and what you expect to get back? It's still a little vague, and I'm thinking that with more info we might be able to help a bit better.

Ok - that would make more sense! see attached file. the timesheet tab is where the formula would go - in cells d10:d16. The employee code would be entered in cell c4. The formula would look up the employee code, the date (b10:b16) and the leave type, of which there are several (listed in the leave type tab). The formula will only return the leave type if one is found for a particular employee for a particular day.
So for employee 456665 on 31 March, it would return "sick"
To put it another way, the formula is asking - tell me what type of leave was taken, if any, by this employee on this day.
Thanks for your help.

Does this formula work for you?


copied down

note this is set for File!$A$4:$A$500... you may need to alter to suit bigger range....

Thats perfect!! Thanks!
If its not too much hassle, could I ask you how the formula works?

Firstly, I hastely put up the formula before making minor tweaks.... so here is the cleaner version:


The inner most MATCH() function takes advantage of your concatenated values in the File sheet and matches each of thosesvalue in File!$A$4:$A$500 to each value in the combination of $C$4&$B10&'leave types'!$B$3:$B$8 and when copied down the B10 value changes (i.e date only changes).

The result of the MATCH() function is an array of #N/A values where matches were not found and possibly some numeric results signifying a match was found and identifying in which position of the 'leave types'!$B$3:$B$8 range the match(es) were found.

LOOKUP(9.9999E+307,MATCH()) is then used to identify what the last numeric value, if any, is. Lookup() looks for the last value in a range that is less than or equal to the lookup value (in this case the largest number Excel recognizes, 9.9999E+307)... Lookup also ignores errors like #N/A. So, basically this Lookup should find the number that matches the position in 'leave types'!$B$3:$B$8 (if more than one exists, it will take the last).. and then Indexes it in that same range to return the actual text string at that position.

The other LOOKUP... LOOKUP(REPT("z",255),CHOOSE({1,2},"", is used as an error trap.... working in a similar fashion, this lookup looks for the last text string in range {"",result of the INDEX() function} and returns the last that is smaller than "z" repeated 255 times.... so if the INDEX() doesn't find a match and returns a #N/A error, then Lookup ignores the error and returns a blank ("")... otherwise, if a text string was returned, then that result is returned.

Incidentally, if you have Excel 2007 or 2010, you can use IFERROR instead:


Hope that helps a bit.

Note: You can use the Formula evaluator tool (in 2003, in Tools|Formula Auditing|Evaluate Formula. In 2007+, in Formulas tab, Formula Auding section, Evaluate formula). Please reduce the $A$4:$A$500 range first to about $A$4:$A$20 and use the 456665 ID so that it is easier to follow the logic.

Thanks for the detail!
Bit easier to follow also with the 2007/2010 version also.

Cheers

NBVC, that is nice work!