INDEX MATCH MATCH

INDEX MATCH MATCH

Say you want to pull a value from a table based on inputs cells. Both the row and column choices are dependent upon those inputs.

Example:

You have a list of food items in column A. Prices for those items are dependent on the month, January-December, and are listed in columns B-M. You want to be able to set up input cells were you could choose to lookup, say, the price of bananas in March. How can you do this?

INDEX MATCH MATCH

= INDEX ( entire matrix , MATCH ( vertical lookup value,  entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )

Learn more about it here:
http://www.mbaexcel.com/excel/how-to-use-index-match-match/

Not really an Excel tip...

This can be used in any Office program, to add a little zing to whatever you are creating. Have you ever wanted to create a number in a circle? Windings 2  is your answer:

http://speakingppt.com/2011/10/24/cheat-sheet-for-creating-numbers-inside-circles/

BTW, am I the only one who always says WingDings?

Search terms and phrases:
Number in Circle
Windings font

VBA for Various Copy/Paste Functions

I've been playing around with VBA a bit for a model I'm building. I needed a quick reference for various copy/paste functions, and found this website to be very helpful and easy to follow:

https://support.microsoft.com/en-us/kb/291308

Which is unusual, seeing how it's just the MS website. Still worth a look!

Search terms and phrases:
VBA
Copy Paste Range

Using a Pivot Table to count unique records

Pivot tables have always had a major problem- when you try to count unique records, it will give you the total number of records.

Example: say you have a database of service records, including service provider name and state. If you wanted to determine how many service providers are in a particular state, a pivot table would instead give you the total number of records for that state, even if you had service provider set as a count.

Until now!

Starting with Excel 2013, there's a workaround to get the actual unique value count.

http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/

Search terms and phrases:
Unique count pivot table Excel
Distinct count pivot table

COUNT and SUM by color!

I'm not going to lie, this trick blew my mind. Have you ever needed to count or sum cells based on the color of the cell? I've always done a filter by color, and then created a new column with some sort of key as to whether to include it in my calculations. Well, someone much smart than I am created a macro that creates a new function: COLORFUNCTION. Enjoy!

http://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html

Search terms and phrases:
Excel count based on color
Excel sum by cell color

Returning data to the right of a character

Have you ever needed to return data in a text string that was located after a specific character? Here is an easy way to do it without using text to columns (which, depending on your data set, isn't always an option).

http://www.ozgrid.com/forum/showthread.php?t=60189

Search terms and phrases:
Excel return data to right of character
Separate data based on character Excel

Splitting Text and Numbers

I've got a really annoying problem at work that occurs in a lot of our data. Our store numbers are usually embedded within a longer string of text. Something like "Your town 000345 Anyplace ABC." Sometimes there is a # before the data, but not always, so a simple text to columns won't work.

In a search for efficiency, I came across this site. Basically, it's a formula that searches for the first number and returns the position of that first number. Then, you can use that information to return all characters to the right (when, combined with a LEFT function can get me the six digit store number I need). This has saved me so much time.

https://exceljet.net/formula/split-text-and-numbers

Search terms and phrases:
Isolating text from numbers in Excel
Separating numbers from text Excel
Return numbers in middle of text string

Welcome!

Welcome to Help With Excel! The purpose of this blog is twofold:

1. To help me keep track of the useful Excel tips I come across
2. To share those useful tips and tricks with fellow Excel nerds.

Please note that I'm currently running Excel 2013 (Windows), but many of the tips I find should be valid for other versions as well.

I hope you enjoy this site!