Solved

Retrieving values when two variables are present in Excel

Posted on 2014-10-13
15
87 Views
Last Modified: 2014-10-30
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
0
Comment
Question by:WeThotUWasAToad
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40379063
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40379296
This would be so much easier if you were to post a workbook rather than pictures. ;)
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40379633
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
 
LVL 22

Expert Comment

by:Flyster
ID: 40381340
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
 

Author Comment

by:WeThotUWasAToad
ID: 40381413
(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
 

Author Comment

by:WeThotUWasAToad
ID: 40381441
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
 

Author Comment

by:WeThotUWasAToad
ID: 40381464
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40381550
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 167 total points
ID: 40381628
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
 
LVL 22

Accepted Solution

by:
Flyster earned 333 total points
ID: 40381897
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
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 333 total points
ID: 40382082
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
 

Author Comment

by:WeThotUWasAToad
ID: 40382710
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
 

Author Comment

by:WeThotUWasAToad
ID: 40382760
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40382873
I did it by hand as it was a small data set. For bigger data I would code it.
0
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40414270
Thanks for the solutions.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now