This one's not only got me stumped but I don't even know where to begin.
In a case with a single variable (ie where a user inputs only one value), I can often find a solution using =VLOOKUP(). However, in this case, two variables are present so it will require more than what's in my bag of tricks.
Also, I have a suspicion that the format which currently exists in my spreadsheet (and is shown in the screenshots I've included) is not really conducive to the lookup process. Therefore, I suppose part of this question relates to how best to reorganize what is present into a more usable form.
As is usually the case, I'm hopeful that a solution can be provided which uses only Excel formulas and not VBA. I realize that's not always possible but if it is, that is my preference.
Suppose you've got a spreadsheet which contains a list of several hundred items (in column B), each of which is identified by a unique 4-character code. And suppose that for some specific period of time in the past, each item may
have belonged to one or more of five categories, A thru E (Fig. 1.):
Now suppose you designate two fields (cells), as shown in the next screenshot (Fig. 2), for a user to enter an item code (blue box) and a year (red box):
What formula in the third cell (yellow box) would display the category, if any, to which an item belonged during the year specified?
For example, say the user enters KAHD as the item number and 2001 as the year. The yellow result box should display Category D since Item KAHD belonged to that category for the time range 1997-2007 (Fig. 3).
Alternatively, suppose the user keeps the same ID code but changes the year to 1991. In that case, the yellow box should not return any category because that item was not categorized at that time (Fig. 4):
This same result would also occur for any
entered year if the item has never been categorized in a group (Fig. 5):
Some items were categorized in one group for a period of time and then subsequently switched to another category. This is the case for Item QFRN where the user-entered year of 1998 would yield the result Category E (Fig. 6)
…but changing to the year 2009 would return Category C (Fig. 7)
And finally, it's possible for a given item to simultaneously be categorized in more than one group. That is the case for Item DQCJ which was in Category D for the years 1992-2001 and Category A during 1996-2004. In this case, if the user were to enter a year during the overlap (1996-2001), the result box should display the higher of the two or more categories (ie category priority is A < B < C < D < E) as shown here (Fig. 8):