Vlookup on a Subtotal

Have you ever needed the vlookup ability on a subtotal? Here's a rather quick way (once you get used to typing it out), to return a value from a subtotal sheet. There's one more step added to get rid of the #NA.

Check out the example.

http://www.mediafire.com/file/yj0vojvz5qu/VlookupSubtotal.xlsx

Tyler

Return List based on Criteria

We've all used AutoFilter to sift through data right?

I've often struggled to be able to return a list of data based on a criteria -- by NOT using AutoFilter. The reason i'm against using AutoFilter is because you must have all the data on the page you're working with -- and the data you dont need is still there -- just hidden. I've seen others in the past use a Macro to cut out visible cells and paste to another page, etc...

Finally, from scouring the internet and putting pieces together, I came up with a pretty long forumla that will return a list of data based on a criteria. With the result, you can actually use to calculate with -- sum, avg, etc...

You can download a basic example of what I'm explaining @ http://www.mediafire.com/file/iwmfyit55oz/ListCriteria.xls

http://www.mediafire.com/?iwmfyit55oz

Be sure when you edit the formulas using INDEX, ROW and SMALL -- you are working with arrays so when you would normally press ENTER when finishing editing the cell -- you must press CTRL + SHIFT + ENTER. You may use the corner box to drag-down.

You dont necessarily need to understand how this formula works, just use the example spreadsheet and make it work for you.

Tyler B