Search results

One of the trickier problems in Excel is to count rows in a set of data with "OR logic". There are two basic scenarios: (1) you want to count rows where a value in a column is "x" OR "y" (2) you want to count rows where a value, "x", exists in one column OR another.

In this example, the...Read more

By default, Excel is not case-sensitive.  For example, with "APPLE" in  A1, and "apple" in A2, the following formula will return TRUE:

=A1=A2 // returns TRUE

To compare text strings in a case-sensitive way, you can use the...Read more

This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate "2nd column", and zero as the last argument to force an exact match.

Although in this case we are mapping text values to numeric outputs, the same formula can...Read more

Working from the inside out, the MIN function is used to find the lowest bid in the range C5:C9:

MIN(C5:C9) // returns 99500

The result, 99500, is fed into the MATCH function as the lookup value:

MATCH(...Read more

In this example, the goal is to look up and retrieve all names for a given team and return them in a single cell as a comma‑separated list. At the core, this is a lookup problem, but the twist is that we want to return multiple matches for each team, not just one. That means...Read more

At the core, this is a normal INDEX and MATCH function:

=INDEX(array,MATCH(value,range,0))

Where the MATCH function is used to find the correct row to return...Read more

This formula uses the value in cell E5 for a lookup value, the named range "key" (H5:I9) for the lookup table, 2 to indicate "2nd column", and 0 as the last argument indicate exact match. You can also use FALSE instead of zero if you like.

VLOOKUP simply...Read more

The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name.

Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named range "...Read more

Excel supports the wildcard characters "*" and "?", and these wildcards can be used to perform partial (substring) matches in various lookup formulas. However, if you use wildcards with a number, you'll convert the numeric value to a text value. In other words,...Read more

In this example, we have a table of employee locations like this on Sheet2:

...Read more

This formula uses -1 for match type to allow an approximate match on values sorted in descending order. The MATCH part of the formula looks like this:

MATCH(F4,B5:B9,-1)

Using the lookup value in cell F4, MATCH finds the first value in B5:...Read more

The IFERROR function is designed to trap errors and perform an alternate action when an error is detected. The VLOOKUP function will throw an #N/A error when a value isn't found.

By nesting multiple VLOOKUPs inside the IFERROR function, the formula allows for sequential lookups. If the...Read more

This is a basic example of VLOOKUP in "exact match" mode. The lookup value is B6, the table is the range B5:C7, the column is 2, and the last argument, FALSE, forces VLOOKUP to do an exact match. (Read why this is necessary here).

...Read more

This is a standard VLOOKUP formula. It requires a table with lookup values (in this case, dates) to the left of the values being retrieved.

The lookup value comes from cell E6, which must be a valid date. The table array is the range B6:C11, and the column index is 2, since the amounts...Read more

The goal is to demonstrate how other formulas and functions can be nested inside the IF function. The example is a simple quantity-based discount formula.

IF function

The...Read more

This is a standard "exact match" VLOOKUP formula with one exception: the column index is calculated using the COLUMN function. When the COLUMN function is used without any arguments, it returns a number that corresponds to the current column.

In this case, the first instance of the...Read more

In this example, the goal is to map the numbers 1-6 to the arbitrary values seen in the table below. For example:

• If the input is 1, the output should be 10
• If the input is 2, the output should be 81
• If the input is 3, the output should be 17
• If the
...Read more

In this example, the goal is to calculate the correct commission for both Agent and Broker based on a 3% commission which is split according to the table in G7:I11, which is named split. Notice the amount going to the Agent and Broker changes as the total amount increases, which...Read more

This formula relies on a table with columns for both the full state name and the 2-letter abbreviation. Because we are using VLOOKUP, the full name must be in the first column. For simplicity, the table has been named "states".

VLOOKUP is configured to get the lookup value from column C...Read more

In this example, the goal is to retrieve employee information from a table using only a partial match on the last name. In other words, by typing "Aya" into cell H4, the formula should retrieve information about Michael Ayala.

The VLOOKUP function...Read more

In this example, the goal is to calculate the correct grade for each name in column B using the score in column C and the table to the right. For convenience only, grade (G5:G9) and score (F5:F9) are named ranges. This is...Read more

In this example, the goal is to group ages into buckets. One way to do this is to prepare a table with age breakpoints in the first column, and the name of the appropriate group or bucket in the second column. Then use a lookup function to find the right bucket or group for each age. In the...Read more

In this example, the goal is to perform a two-way lookup based on the name in cell H4 and the month in cell H5 with the VLOOKUP function. Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can create a dynamic column index...Read more

Note: a simpler approach would be to alter the table used by VLOOKUP directly. But this example explains the mechanics of testing and overriding output from VLOOKUP.

This formula is based on a simple grading example explained in...Read more

The goal in this example is to create a self-contained lookup formula to assign a grade to the score in cell E7, based on the table in B6:C10. However, instead of providing B6:B10 as a reference for the table_array argument, the table is provided as a constant....Read more