Lookup lowest value
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
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
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
This formula uses the value in cell F6 for a lookup value, the range B5:C10 for the lookup table, 2 to indicate "2nd column", and zero as the last argument to force an exact match.
Although in this case we are mapping numeric inputs to numeric outputs, the same formula will handle text...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
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
The Excel DROP function returns a subset of a given array by "dropping" rows and columns. The number of rows and columns to remove is provided by separate rows and columns arguments. Rows and columns can be dropped from the start or end of the given array....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.
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 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
The Excel CHOOSECOLS function returns specific columns from an array or range. The columns to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a column in the given array.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
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.
Normally...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
One of the VLOOKUP function's key limitations is that it can only lookup values to the right. In other words, the column that contains lookup values must sit the the left of the values you want to retrieve with VLOOKUP. There is no way to override this...Read more
Working from the inside out, the IF function in this formula, which is entered as the "table_array" argument in VLOOKUP, runs a logical test on the value in column C "Years", which represents the number of years a salesperson has been with a company. If C5 is less than 2, then table1 is...Read more
In this example, the goal is to configure VLOOKUP to perform a lookup in a table where the first column contains numbers entered as text, and the lookup value is a true number. This mismatch between numbers and text will cause VLOOKUP to return an #N/A error.
Typically, the lookup column...Read more
The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.Read more
In this example, the goal is to use VLOOKUP to find and retrieve price information for a given product stored in an external Excel workbook. The workbook exists in the same directory and the data in the file looks like this:
...Read more
In this example, the goal is to count codes in a case-sensitive way. The COUNTIF function and the COUNTIFS function are both good options for counting text values, but neither is case-sensitive, so they can't be...Read more