Xor, this formula looks like exactly what I'm needing for a project of mine. Can you please explain how it works?



I tried replacing the names with the actual array, but that seems to break the formula. For example, I replaced "d" with Services!$B$2:$B$221, and now the formula is only working for the very first example Alawa Preschool. Maybe you can shed some light on why this isn't working past the first match, even though the "d" name is only referencing the services B2 to B221.

What I'm needing is to be able to sort items in a list into various customers for a report. Right now we currently have four customers, which each have their own tabs for a report that I send them daily. So, if a list of "sales" for the day can come from any of these customers, I'm needing a formula that will only grab sales for a specific customer, and then I'd modify that customer on each tab. Something automatic instead of trying to use a filtered pivot table, which can get messy.

I'd rather learn the logic behind what is happening so I can use it for other problems as well. I just recently learned the trick of combining Index and Match for a more flexible alternative to VLookup, which looks similar to what you're doing here. I've not used the SMALL, ROW, CELL, or ROWS formulas you're using here, so would appreciate a brief tutorial of how everything is working together.

Edit mod : this post refers to https://www.msofficeforums.com/excel...t-only-if.html

Hi
please do not hijack an existing thread. Create a new one referring to an existing one if necessary (I did it for you this time)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

See explanation in the file.

Attached Files

Explain_bex_77.xlsx (28.2 KB, 9 views)

Sorry, used to other forums where asking questions about a similar subject as other posters gets heated redirects to the original question. I guess I'm getting a heated reply telling me to create a new thread.

What about a tiny response Lluewhyn?

I actually used quite some time trying to help you with something you yourself asked for help to!

Sorry, I was off of work yesterday, and read your response late on Wednesday. You're making me feel really guilty by the length of your response, but I very much appreciate the work you put into explaining the logic. I typed out my reply right before leaving work, and realized later I never properly thanked you for your time and effort.

I imagine today will be slow enough for me to delve fully into the spreadsheet to pick apart the various formulas and how they work together. Hopefully, I'll be able to do that along with your descriptions so I can figure this out on my own without bothering you any more.