# Category Archives for "Excel – Functions and Formulas"

## 19 Top Tips on Excel’s IF Functions and Formulas

Excel’s IF function makes If…Then… decisions in your workbook formulas. The IF function and its variations are a must if you want to build powerful worksheets that go beyond simple math or financial calculations. If you already consider yourself an IF function expert, you might want to check Tips 7, 9, 13, 14, and 19 […]

## Top 16 Best Excel Function and Formula Tips Every Excel User Should Know

These are the top 16 Excel function and formula tips every Excel user at any level of experience should know. Even “experts” can learn new tricks or combine old tricks to work faster and more efficiently. As you scan these tips keep a “beginner’s mind.” A beginner’s mind helps you see new opportunities and insights. […]

## Top 21 VLOOKUP Tips and Tricks

Use VLOOKUP to find and retrieve data from an Excel list. This list of the top 21 best VLOOKUP tips will take you from novice to master in retrieving data from Excel lists. You can use VLOOKUP to, Look up prices, names, or regions from a list Use one item to find related items in […]

## Creating and Checking Dynamic Named Ranges using Excel’s OFFSET Function

Use Excel’s OFFSET and COUNTA functions to create dynamic range names that automatically adjust as data is added or deleted. Normally a named range refers to a fixed set of cell references, for example, \$B\$7:\$B\$12. However, if the OFFSET formula is used to calculate the cell reference, the range name will change as the data […]

## Don’t Make this Mistake Using Excel’s OFFSET Function

Excel’s OFFSET function will give you errors if you follow some instructions on the internet. Even the Microsoft Support page found here makes this mistake, How to use defined names to automatically update a chart range in Excel https://support.microsoft.com/en-us/kb/183446 In their example, Microsoft puts all data in one column and references that data column in […]

## Using VLOOKUP with multiple criteria from drop-down lists

VLOOKUP cannot accurately retrieve data from tables where the leftmost column does not have unique text values. But, with this trick you can use two (or more) Data Validation lists to select unique data from a table. This VLOOKUP trick works well in situations where you need to specify criteria from two columns in the […]

## Using VLOOKUP and MATCH for a two-way look up across the column header

Most VLOOKUP formulas specify an exact column to pull the data from, but if you use MATCH it’s easy to pull the data from any column. Another advantage is that the formula automatically adjusts if you move columns in the table to new positions. In the following figure the table is in F10:R13. The user […]

## Troubleshooting errors in VLOOKUP

If the lookup_value is not found in the leftmost column, VLOOKUP returns a #N/A error. There are simple ways to either prevent or handle this error. The best method is to use the Data Validation command to give the user a list of allowable entries. To create a Data Validation list, 1. Select the lookup_value […]

## Using VLOOKUP to retrieve data from an Excel table

VLOOKUP is a simple way to retrieve data from a table in Excel, but you must understand its limits. VLOOKUP works by looking down the left column of the table’s range until it finds a match for the lookup_value, then it looks across that row to the cell in the column you specify. Download the […]

## Comparing VLOOKUP, INDEX, and MATCH

VLOOKUP and the combination of MATCH and INDEX retrieve cell contents from a table. But, there are important differences between the two. You need to know when to use VLOOKUP and when to use INDEX and MATCH because each has unique abilities. Here’s a quick decision table that will help you know when to use […]