Results 1 to 10 of 10

Thread: Lookup help

  1. #1

    Lookup help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'd probably just use a hash column to generate a unique lookup code, then hit it with a VLOOKUP.

    Example attached.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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.
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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.
    Attached Files Attached Files

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,498
    Articles
    0
    Excel Version
    Excel 2016
    Does this formula work for you?

    Code:
    =LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX('leave types'!$B$3:$B$8,LOOKUP(9.9999E+307,(MATCH(File!$A$4:$A$500&"",$C$4&Timesheet!B10&'leave types'!$B$3:$B$8&"",0))))))
    copied down

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


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

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,498
    Articles
    0
    Excel Version
    Excel 2016
    Firstly, I hastely put up the formula before making minor tweaks.... so here is the cleaner version:

    Code:
    =LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX('leave types'!$B$3:$B$8,LOOKUP(9.9999E+307,(MATCH(File!$A$4:$A$500,$C$4&$B10&'leave types'!$B$3:$B$8,0))))))
    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:

    Code:
    =IFERROR(INDEX('leave types'!$B$3:$B$8,LOOKUP(9.9999E+307,(MATCH(File!$A$4:$A$500,$C$4&$B10&'leave types'!$B$3:$B$8,0)))),"")
    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.


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

    Cheers

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    NBVC, that is nice work!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •