Complex Excel vlookup issue... any ideas?

Discussion in 'OT Technology' started by 360, Aug 8, 2007.

  1. 360

    360 OT Supporter

    Mar 13, 2007
    Likes Received:
    I'm working with two spreadsheets, one with a large amount of information regarding specific individuals, and the other being a blank template I was required to populate with the info from the first.

    The focus at this moment in the individuals info sheet are their educational history (ie, school name, degree type, date degree issued... and so on, with regards to how many degrees they have), and we're talking about 3200+ people, so 3200+ rows of info.

    Populating the template is simple enough for the most part (using vlookups), but not so much for the educational info. The thing is, the template I need to populate requires the EARLIEST degree they acquired and the LATEST. This makes it tough when you have some people occupying cells with more than 4 degrees, etc.

    In the template, as an example, I used the MIN function across the "date degree issued" in one person's row to find the earliest date, and the MAX function accordingly to find the latest.

    The problem, however, lies with the VLOOKUPs. The order of the info I need goes: Date Degree Issued - Degree Type - School Name

    So, I could do a VLOOKUP on the date I populated in the template (using the MIN/MAX function) for the corresponding degree and find the degree type and school name, but the ranges will obviously be different for each one, and for each person... So I can't just copy this function "down the rows" and expect all the results to be accurate. And I certainly can't do this row-by-row, as it would take entirely too long!

    I've been racking my brain over this for a few days now. I know theres a simple solution, but I'm just burnt out at this point.

    Is there a way I can refer to the location of the MIN/MAX date on the "info" sheet for the range of the VLOOKUP? Something to that degree would make things easier.

    Sorry if this is confusing guys, please let me know if anybody has any ideas. I used to consider myself a wiz before this challenge! [​IMG]
  2. jimbo_jones

    jimbo_jones New Member

    Aug 31, 2006
    Likes Received:
    Can you attach some representative subset of the data so that others could try to accomplish what you require?
    My first thought is that if you can use the 'min' and 'max' functions, then you might be able to nest an 'if' statemtent into the 'vlookup' function to determine which column it is looking for.

Share This Page