Excel - INDEX Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula that will pull items from a certain column, depending on two other criteria related to two other columns, and print out every instance that matches all of the above once and only once, and stop printing when all instances are printed. Currently have been relying on this for half of the job (printing one, and only one, instance of every unique element), but have not been able to incorporate the other two criteria: =ArrayFormula(index(RawData!$E$2:$E$500000, match(0, COUNTIFS($C$1:$C12,RawData!$E$2:$E$5000),0)))
Solved by I. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 07/06/2018 - 04:38
Hello and welcome to Got it! :)
User 07/06/2018 - 04:40
User 07/06/2018 - 04:40
how does this work
Excelchat Expert 07/06/2018 - 04:40
Okay, I've seen your query, and I believe you need to modify your existing formula to consider two other criteria. Is this correct? :)
User 07/06/2018 - 04:41
yes, that is correct, unless there is another formula
Excelchat Expert 07/06/2018 - 04:42
The formula I was thinking of was actually similar to your formula, but with a few modifications. Will it be okay if we look at the file you're working on to get a better picture on this? Thanks! :)
User 07/06/2018 - 04:44
Sure, getting it online now
Excelchat Expert 07/06/2018 - 04:45
Okay, noted on that! :)
User 07/06/2018 - 04:47
Excelchat Expert 07/06/2018 - 04:48
Okay, already opened the file! What might be the criteria we're looking at here? :)
User 07/06/2018 - 04:50
commented on the file
User 07/06/2018 - 04:50
Excelchat Expert 07/06/2018 - 04:51
Okay I see. I saw your comment, and you wish to match the city and date, then return unique values. Okay, let me have a look and will come up with a formula for this. :)
User 07/06/2018 - 04:52
ok, thank you so much for your help in advance
Excelchat Expert 07/06/2018 - 04:52
Btw, I don't see any data in RawData column E. Can we put some dummy data in there so we could know if we did it right? Thanks! :)
User 07/06/2018 - 04:59
updated the formula
User 07/06/2018 - 04:59
sorry, got pulled away because my complementary time window was closed
Excelchat Expert 07/06/2018 - 04:59
Oh okay, noted on that. :)
User 07/06/2018 - 05:00
how much tim edo you think that it will take for you to finish this out of curiousity
Excelchat Expert 07/06/2018 - 05:01
If all goes well, that 17 minutes will be more than enough. :)
Excelchat Expert 07/06/2018 - 05:01
In worst case scenario, we may need to extend until the last free session. :)
User 07/06/2018 - 05:02
ok, fantastic
Excelchat Expert 07/06/2018 - 05:11
Excelchat Expert 07/06/2018 - 05:12
I've tried pretty much every combination of INDEX MATCH IF statements, and it still won't return a value. Just noticed we don't have any value in Raw Data matching the criteria, and that's why. :D
User 07/06/2018 - 05:12
Excelchat Expert 07/06/2018 - 05:13
I was nearly draining my sanity as to why it's not working. Turns out, we should need to change some data so we could test this one. :D
User 07/06/2018 - 05:14
i literally spent three hours on this today trying to figure it out, so if you can figure it out in the next ten minutes, I would be over the moon, not to mention will be using this service for a long time to come
Excelchat Expert 07/06/2018 - 05:14
Okay, that's nice! :)
User 07/06/2018 - 05:15
not sure if either of these will help, or if you already know this stuff, but I was looking at the arrays produced and trying to figure out how to combine queries
User 07/06/2018 - 05:15
User 07/06/2018 - 05:15
Excelchat Expert 07/06/2018 - 05:21
Finally, already done. :)
Excelchat Expert 07/06/2018 - 05:22
I just had to retrace the combinations I made since the data we're looking at back then doesn't have any that matches both our criteria. :)
Excelchat Expert 07/06/2018 - 05:23
Btw, i haven't seen the references you posted. Upon looking at them, they won't actually work in our case since they're only good for returning a single value.
Excelchat Expert 07/06/2018 - 05:23
We need to incorporate a COUNTIF function in our formula so that we could return unique data from our reference based on multiple criteria. :)
User 07/06/2018 - 05:24
this looks amazing
User 07/06/2018 - 05:24
give me a minute to go through this
Excelchat Expert 07/06/2018 - 05:24
I hope you wouldn't go over the moon btw, so that you could always return to our service should you need powerful Excel solutions. :)
Excelchat Expert 07/06/2018 - 05:24
Sure thing! :)
Excelchat Expert 07/06/2018 - 05:31
Any update btw? :)
User 07/06/2018 - 05:32
trying to get it to work in my real file (more confidential data, a lot more going on)
User 07/06/2018 - 05:33
how would I do a less than instead of if A1 is equal
Excelchat Expert 07/06/2018 - 05:34
You mean A2 (date) right? You can just change the = sign to <, so that it would be A2<....
User 07/06/2018 - 05:35
a2, hes
User 07/06/2018 - 05:35
but wouldn't I have to do ">"& or something
Excelchat Expert 07/06/2018 - 05:36
Not necessarily, since that only applies for fixed values, and not references. If you look at B2, I changed A2 = to A2 <, and it works fine. :)

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc