Retrieving values when two variables are present in Excel

Hello,

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.):
Fig. 1Now 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):
Fig. 2What 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).
Fig. 3Alternatively, 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):
Fig. 4This same result would also occur for any entered year if the item has never been categorized in a group (Fig. 5):
Fig. 5Some 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)
Fig. 6…but changing to the year 2009 would return Category C (Fig. 7)
Fig. 7And 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):
Fig. 8Thanks
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
Please humor me and see if this works for you:

=IF(AND(H3>=INDIRECT("P"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("Q"&MATCH(G3,B8:B15,0)+7)),"E",IF(AND(H3>=INDIRECT("M"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("N"&MATCH(G3,B8:B15,0)+7)),"D",IF(AND(H3>=INDIRECT("J"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("K"&MATCH(G3,B8:B15,0)+7)),"C",IF(AND(H3>=INDIRECT("G"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("G"&MATCH(G3,B8:B15,0)+7)),"B",IF(AND(H3>=INDIRECT("D"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("E"&MATCH(G3,B8:B15,0)+7)),"A","None")))))

Flyster
0
Rory ArchibaldCommented:
This would be so much easier if you were to post a workbook rather than pictures. ;)
0
Rob HensonFinance AnalystCommented:
As always, it would be better for retrieving information from a data set when that data set is in more of a data grid format. For this you would have columns for:

ID Code | Start Year | End Year | Category

I think you would then be able to use INDEX and MATCH formulas to retrieve data.

Thanks
Rob H
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

FlysterCommented:
Here's a slight modification. This one will handle an occurrence if the item ID entered is not found in the list in column B:

=IFERROR(IF(AND(H3>=INDIRECT("P"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("Q"&MATCH(G3,B8:B15,0)+7)),"E",IF(AND(H3>=INDIRECT("M"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("N"&MATCH(G3,B8:B15,0)+7)),"D",IF(AND(H3>=INDIRECT("J"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("K"&MATCH(G3,B8:B15,0)+7)),"C",IF(AND(H3>=INDIRECT("G"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("G"&MATCH(G3,B8:B15,0)+7)),"B",IF(AND(H3>=INDIRECT("D"&MATCH(G3,B8:B15,0)+7),H3<=INDIRECT("E"&MATCH(G3,B8:B15,0)+7)),"A","None"))))),"None")
0
WeThotUWasAToadAuthor Commented:
(Note:  Flyster, this post applies only to your first response. Most of it was written earlier today before your second response — which I have not yet tested — but I decided to go ahead and post it anyway.)

"Please humor me…"
Wow! This may be "humoring" for you but I am nothing short of amazed. Now I'm going to have to break down ("parse" << is that the right word?) this very long formula to figure out what it's doing. (That's not a complaint but a comment re a fun and challenging project.)

"…and see if this works…"
Hmm, how shall I say this? How about:  

        "…er…um…yeah, sorta…"

or perhaps:

        "yes & no"?
:)

For example, entering ASAO & 1998 worked great:
Fig. 9but ZVOA & 1998 did not:
Fig. 10DNCT & 2012 (overlapping years) did:
Fig. 11and KSMK & 2012 (which is below the rows displayed in the earlier screenshots) did not:
Fig. 12
0
WeThotUWasAToadAuthor Commented:
Thanks for all the responses.

This would be so much easier if you were to post a workbook rather than pictures. ;)
Happy to oblige Rory (it's attached) but I'm curious to know (and for future threads) if your statement applies to files which have been "dry-labbed" as we…er…I mean others used to call it back in school. It means starting with what the results should be and then fabricating the equations and data, etc, backwards so they fit with the beginning conditions.

That's all the attached file is — as you will see. In other words, I created the chart and then manually entered the values I'm wanting to obtain later (ie once I get an EE solution formula(s). In fact, that's what most of the screenshots I post on EE are — since the original files commonly contain confidential contents, as is the case with this one.

So the question I guess is:

Do you want files even if they are fabricated?

c-EE-uploaded-2014-10-14.xlsx
0
WeThotUWasAToadAuthor Commented:
As always, it would be better for retrieving information from a data set when that data set is in more of a data grid format.
Rob, I'm glad you mentioned this because it's something I included in my initial post:
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.
The actual data is currently arranged in the format or configuration shown in my screenshots. I recognized it to be awkward but, aside from changing it manually (which is impractical due to the large number of entries), I'm not sure how to utilize Excel formulas to effect a change.

Flyster has (remarkably) bypassed that step but rearranging is something in which I'm still interested since it will cause other future operations and formulas to be simpler.

I'm happy to post this rearranging question in a new thread if you recommend it…

…especially since, as Rory knows, I've already got all the screenshots.  hehe   ;)
0
Rory ArchibaldCommented:
Do you want files even if they are fabricated?

Yes. As long as they are accurately representative of your data so they can be used for developing and testing solutions. It's a lot simpler and less effort (in total) for you to create that than it is for each of us to create our own file, based on what we think you have. :)
0
Rory ArchibaldCommented:
With your existing layout, you can do this:

1. Add two new named ranges:

ItemRow1: =INDEX(h!$D$8:$P$22,MATCH(h!$G$3,h!$B$8:$B$22,0),0)
ItemRow2: =OFFSET(ItemRow1, 0, 1)

Then in J3:

=IFERROR(LOOKUP(2,1/(MOD(COLUMN(ItemRow1)-3,3)=1)/(ItemRow1<=H3)/(MOD(COLUMN(ItemRow2)-3,3)=2)/(ItemRow2>=H3),$D$6:$Q$6),"None")

I've also added a revised layout with just 4 columns on a new tab which makes the formulas a lot simpler! You need to ensure that the categories are sorted in ascending order (so Cat E for an item appears below Cat D etc).
c-EE-uploaded-2014-10-14.xlsx
0
FlysterCommented:
Yes, I found one typo in my code. ZVOA was not working because the range was set to G:G and not G:H. KSMK did not work because, working off of the screen shot, the range was set to row 15, not 22 as needed. This formula should fix the above mentioned errors:

=IFERROR(IF(AND(H3>=INDIRECT("P"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("Q"&MATCH(G3,B8:B22,0)+7)),"E",IF(AND(H3>=INDIRECT("M"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("N"&MATCH(G3,B8:B22,0)+7)),"D",IF(AND(H3>=INDIRECT("J"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("K"&MATCH(G3,B8:B22,0)+7)),"C",IF(AND(H3>=INDIRECT("G"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("H"&MATCH(G3,B8:B22,0)+7)),"B",IF(AND(H3>=INDIRECT("D"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("E"&MATCH(G3,B8:B22,0)+7)),"A","None"))))),"None")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FlysterCommented:
Although the formula appears long, it is really nothing more than a nested IF statement. MATCH is used to look up the Item ID value in column B. For "JKLM", the value returned from the MATCH function is 1, as it is the first value in that range. As your range starts in row 8, a "+7" is added to give the row number associated with that ID. The INDIRECT function is used to create a cell reference. Taking the first one listed in the formula, H3>=INDIRECT("P"&MATCH(G3,B8:B22,0)+7)," assuming JKLM was selected in G3, it is looking for a value in P8 that is equal to or greater than the value listed in H3. Using "IF(AND", the formula then goes to the next part, "H3<=INDIRECT("Q"&MATCH(G3,B8:B22,0)+7)),"E",", and looks for a value in Q8 that is equal to or less than the value in H3. If both conditions are True, then the value returned is "E". If False, then it goes to the next set of conditions, comparing the values in M8 & N8. This will continue, stopping when a True condition is met or when it reaches the end, that is it looked in D8 & E8 and found no matches. In this case it returns the False condition, "None". The last modification I added, IFERROR, handles the condition where an ID is enetered into G3 that isn't listed in colmn B. Without it, if, say, ABCD was entered in G3, J3 would return "# N/A". As programmed, it now returns "None", which is the last part seen in the code. You can change that to what ever message you want to send your users to let them know the code is not in the list. Hope this makes sense!
0
WeThotUWasAToadAuthor Commented:
I've also added a revised layout with just 4 columns on a new tab which makes the formulas a lot simpler!
Thanks Rory, that is a lot simpler — but…

How did you do it or what steps/formulas did you use?

In other words, did you just move things around manually by copy/pasting, etc or did you use formulas in some way?

Most importantly, could you have rearranged the data in the same way if the spreadsheet had a few thousand rows rather than the 15 or so in the demo?

Thanks
0
WeThotUWasAToadAuthor Commented:
This formula should fix the above mentioned errors
Thanks Flyster, yes it did fix the errors and now works great.

Thanks also for the explanation. That's very helpful along with arranging your formula as shown below so it's easier to follow. I'm pasting it here in that form only for my own future reference:

=IFERROR(
        IF(AND(H3>=INDIRECT("P"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("Q"&MATCH(G3,B8:B22,0)+7)),"E",
        IF(AND(H3>=INDIRECT("M"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("N"&MATCH(G3,B8:B22,0)+7)),"D",
        IF(AND(H3>=INDIRECT("J"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("K"&MATCH(G3,B8:B22,0)+7)),"C",
        IF(AND(H3>=INDIRECT("G"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("H"&MATCH(G3,B8:B22,0)+7)),"B",
        IF(AND(H3>=INDIRECT("D"&MATCH(G3,B8:B22,0)+7),H3<=INDIRECT("E"&MATCH(G3,B8:B22,0)+7)),"A",
        "None"))))),
"None")

Open in new window

0
Rory ArchibaldCommented:
I did it by hand as it was a small data set. For bigger data I would code it.
0
WeThotUWasAToadAuthor Commented:
Thanks for the solutions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.