Convert column letter to number
The first step is to construct a standard "A1" style reference using the column letter, by adding a "1" with concatenation:
B5...Read moreDave Bruns
Convert column number to letter
In this example, the goal is to convert an ordinary number into a column reference expressed in letters. For example, the number 1 should return "A", the number 2 should return "B", the number 26 should return "Z", etc. The challenge is that Excel can handle over 16,000 columns, so the number of...Read more
Count occurrences in entire workbook
In this example, the goal is to count the value in cell B5 ("Steven") in the sheets listed in B11:B13. The workbook shown in the example has four worksheets in total. The first sheet is named "Master" and contains the search string, the range, and the sheets to include in the...Read more
Search multiple worksheets for value
The range B7:B9 contains the sheet names we want to include in the search. These are just text strings, and we need to do some work to get them to be recognized as valid sheet references.
Working from the inside out, this expression is used to build a full sheet reference:
...Read more
Search entire worksheet for value
The second sheet in the workbook, Sheet2, contains 1000 first names in the range B4:F203.
...Read more
Match next highest value
This formula is a standard version of INDEX + MATCH with a small twist.
Working from the inside out, MATCH is used find the correct row number for the value in F4, 2100. Without the third argument, match_type, defined, MATCH defaults to approximate match and returns 2.
The small...Read more
Count not equal to multiple criteria
In this example, the goal is to count rows in a set of data using multiple criteria and "not equals to" logic. Specifically, we want to count males that are not in group A or B. All data is in an Excel Table named data in the range B5:...Read more
How to use VLOOKUP to merge tables
This video shows how to use VLOOKUP to merge data in two tables based on a common ID. You can even use this technique to reorder the columns you retrieve.
How to build a simple summary table
Pivot Tables are fantastic tools for summarizing data, but you can also use formulas to build your own summaries using functions like COUNTIF and SUMIF. See how in this 3 minute video.
VLOOKUP with two client rates
This formula is composed of two lookups for the same client. The first lookup finds the onsite rate for the client in column B and multiplies the result by the number of hours in column C:
=VLOOKUP(B5,rates,2,0)*C5
The second lookup finds...Read more