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
The Excel IF function performs a logical test and returns one result when the logical test returns TRUE and another when the logical test returns FALSE. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...Read more
The Excel MIN function returns the smallest numeric value in the data provided. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.Read more
The Excel ABS function returns the absolute value of a number. ABS converts negative numbers to positive numbers, and positive numbers are unaffected.Read more
INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria...Read more
The Excel ISTEXT function returns TRUE when a cell contains a text value, and FALSE if the cell contains any other value. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text.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
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
In this example, we have a table of employee locations like this on Sheet2:
...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
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.
The...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
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:
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 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 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 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