Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • Get column name from index in Excel Table
  • Use Data Form to input, edit and delete records in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Everything about Charts in Excel

References

  • Excel Advanced Lookup using Index and Match Functions
  • Left Lookup in Excel
  • How to use Excel MMULT Function
  • Basic INDEX MATCH approximate in Excel
  • Lookup entire row in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only
  • Excel Data validation must begin with
  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days

Category: Lookup and Reference Functions

Excel comes with multiple Lookup and Reference functions that let you find matching values. The Lookup and Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets

How to reference named range different sheet in Excel

by

This tutorials shows how to reference a named range on another sheet. To achieve this, you can use the INDIRECT function with the required sheet syntax. Formula INDIRECT(“‘”&sheet&”‘!”&name) Explanation In the example shown, the formula in D6 is: =SUM(INDIRECT(“‘”&B6&”‘!”&C6)) Which returns the sum of the named range “data” on Sheet1. How this formula works The …

Continue Reading

Two-way lookup with VLOOKUP in Excel

by

This tutorial shows how to calculate Two-way lookup with VLOOKUP in Excel  using the example below; Formula =VLOOKUP(lookup_value,table,MATCH(col_name,col_headers,0),0) Explanation Note: Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to …

Continue Reading

Lookup entire row in Excel

by

This tutorial shows how to Lookup entire row in Excel using the example below; Formula =INDEX(data,MATCH(value,array,0),0) Explanation To lookup and retrieve an entire row, you use the INDEX and MATCH functions. In the example shown, the formula used to look up all values for the Central region is: =INDEX(C5:F8,MATCH(H5,B5:B8,0),0) How this formula works The gist: …

Continue Reading

Get nth match with INDEX / MATCH in Excel

by

This tutorial shows how to Get nth match with INDEX / MATCH in Excel using the example below; Formula {=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))} Explanation To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to …

Continue Reading

Find closest match in Excel

by

This tutorial shows how to Find closest match in Excel using the example below; Formula {=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))} Explanation To find the closest match with a lookup value and numeric data, you can use an array formula based the INDEX, MATCH, ABS and MIN functions. In the example shown, the formula in E5 is: {=INDEX(data,MATCH(MIN(ABS(data-E4)),ABS(data-E4),0))} where “data” …

Continue Reading

Extract data with helper column in Excel

by

This tutorial shows how to Extract data with helper column in Excel using the example below; Formula =IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),””) Explanation One way to extract data in Excel is to use INDEX and MATCH with a helper column that marks matching data. This avoids the complexity of a more advanced array formula. In the example shown, the …

Continue Reading

Extract all partial matches in Excel

by

This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 …

Continue Reading

How to use Excel ROWS Function

by

This Excel tutorial explains how to use the ROWS function with syntax and examples. Excel ROWS Function Description The Microsoft Excel ROWS function returns the number of rows in a cell reference. The ROWS function is a built-in function in Excel that is categorized as a Lookup/Reference Function. The ROWS function can be entered as part of a …

Continue Reading

Left Lookup in Excel

by

The VLOOKUP function only looks to the right. To look up a value in any column and return the corresponding value to the left, simply use the INDEX and the MATCH function. 1. The MATCH function returns the position of a value in a given range. Explanation: 104 found at position 4 in the range $G$4:$G$7. 2. Use this result and the INDEX function to …

Continue Reading

Convert text string to valid reference in Excel using Indirect function

by

Applying Excel  INDIRECT function to Cell Reference, Range Reference, Named Range, Worksheet Reference. Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings. Cell Reference Use the INDIRECT function in Excel to convert a text string into a valid cell reference. 1. For example, take a look …

Continue Reading

Two-column Lookup in Excel

by

This example teaches you how to perform a two-column lookup in Excel. See the example below. We want to look up the salary of James Clark, not James Smith, not James Anderson. 1. To join strings, use the & operator. 2. The MATCH function returns the position of a value in a given range. Insert the MATCH function …

Continue Reading

Perform case-sensitive Lookup in Excel

by

By default, the VLOOKUP function performs a case-insensitive lookup. However, you can use the INDEX, MATCH and the EXACT function in Excel to perform a case-sensitive lookup. 1. For example, the simple VLOOKUP function below returns the salary of Mia Clark. However, we want to lookup the salary of MIA Reed (see cell G2). 2. The EXACT function in Excel …

Continue Reading

Excel Advanced Lookup using Index and Match Functions

by

Instead of using VLOOKUP, Use INDEX and MATCH and become an Excel pro. . To perform advanced lookups, you’ll need INDEX and MATCH. Match The MATCH function returns the position of a value in a given range. For example, the MATCH function below looks up the value 53 in the range B3:B9. Explanation: 53 (first argument) found at position 5 …

Continue Reading

Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

by

To find the closest match to a target value in a data column, use the INDEX, MATCH, ABS and the MIN function in Excel. Use the VLOOKUP function in Excel to find an approximate match. 1. The ABS function in Excel returns the absolute value of a number. Explanation: C3-F2 equals -39. The ABS function removes the minus sign (-) from a …

Continue Reading

How to use Excel VLOOKUP Function

by

VLOOKUP function is actually quite easy to use once you understand how it works! This Excel tutorial explains how to use the VLOOKUP function with syntax and examples. Excel VLOOKUP Function Description VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. The VLOOKUP function performs a vertical lookup by searching for …

Continue Reading

How to use Excel TRANSPOSE Function

by

This Excel tutorial explains how to use the TRANSPOSE function with syntax and examples. Excel TRANSPOSE Function Description Microsoft Excel TRANSPOSE function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a …

Continue Reading

How to use Excel ROW Function

by

This Excel tutorial explains how to use the ROW function with syntax and examples. Excel ROW Function Description Microsoft Excel ROW function returns the row number of a cell reference. The ROW function is a built-in function in Excel that is categorized as a Lookup/Reference Function. The ROW function can be entered as part of a formula …

Continue Reading

How to use Excel OFFSET function

by

This Excel tutorial explains how to use the OFFSET function with syntax and examples. Excel OFFSET function Description The Microsoft Excel OFFSET function returns a reference to a range that is offset a number of rows and columns from another range or cell. OFFSET function is a built-in function in Excel that is categorized as a Lookup/Reference …

Continue Reading

How to use Excel MATCH Function

by

This Excel tutorial explains how to use the MATCH function with syntax and examples. Excel MATCH function Description MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve …

Continue Reading

How to use Excel MMULT Function

by

This Excel tutorial explains how to use the MMULT function with syntax and examples. Excel MMULT function Description Excel MMULT function returns the matrix product of two arrays. The array result contains the same number of rows as array1 and the same number of columns as array2. If returning multiple results in an array on the worksheet, enter as an …

Continue Reading

How to use Excel LOOKUP Function

by

This Excel tutorial explains how to use the LOOKUP function with syntax and examples. Excel LOOKUP function Description The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used …

Continue Reading

How to use Excel INDIRECT Function

by

This Excel tutorial explains how to use the INDIRECT function with syntax and examples. Excel INDIRECT function Description Microsoft Excel INDIRECT function returns the reference to a cell based on its string representation. The INDIRECT function is a built-in function in Excel that is categorized as a Lookup/Reference Function that can be entered as part of a formula in a …

Continue Reading

How to use Excel FORMULATEXT function

by

This Excel tutorial explains how to use the FORMULATEXT function with syntax and examples. Excel FORMULATEXT function Description FORMULATEXT function can be used in troubleshooting and auditing.  The Excel FormulaText function returns a formula as a text string. Explanation:  The Excel FORMULATEXT function returns a formula as a text string from given reference. You can …

Continue Reading

How to use Excel COLUMN Function

by

This Excel tutorial explains how to use the COLUMN function with syntax and examples. Excel COLUMN function Description The Microsoft Excel COLUMN function returns the column number of a cell reference. The COLUMN function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function in Excel. As …

Continue Reading

Complete List of Excel Lookup and Reference Functions, References and Examples

by

Excel comes with multiple Lookup and Reference functions that let you find matching values. This page provides a complete Excel Lookup and Reference functions list. Each of the function links will take you to a dedicated page, where you will find a description of the function, with examples of use. The Lookup and Reference Functions …

Continue Reading

Count unique text values with criteria

by

This tutorial shows how to Count unique text values with criteria using the example below; Formula {=SUM(–(FREQUENCY(IF(criteria,MATCH(values,values,0)),ROW(values)-ROW(valuesfirstcell)+1)>0))} Explanation To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function. Assume you have a list of employee names together with hours worked on “Project X”, and you want …

Continue Reading

Multi-criteria lookup and transpose in Excel

by

This tutorial shows how to  work Multi-criteria lookup and transpose in Excel using the example below; Formula {=INDEX(range1,MATCH(1,($A1=range2)*(B$1=range3),0))} Explanation To perform a multi-criteria lookup and transpose results into a table, you can use an array formula based on INDEX and MATCH. In the example shown, the formula in G5 is: {=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0))} Note this formula is an array formula and must …

Continue Reading

Merge tables with VLOOKUP in Excel

by

This tutorial shows how to  Merge tables with VLOOKUP in Excel using the example below; Formula =VLOOKUP($A1,table,COLUMN()-x,0) Explanation To merge tables, you can use the VLOOKUP function to lookup and retrieve data from one table to the other. To use VLOOKUP this way, both tables must share a common id or key. This article explains how join …

Continue Reading

Basic INDEX MATCH approximate in Excel

by

This tutorial shows how to calculate Basic INDEX MATCH approximate in Excel using the example below; Formula =INDEX(grades,MATCH(score,scores,1)) Explanation This example shows how to use INDEX and MATCH to retrieve a grade from a table based a given score. This requires an “approximate match”, since it is unlikely that the actual score exists in the table. The …

Continue Reading

Approximate match with multiple criteria in Excel

by

This tutorial shows how to calculate Approximate match with multiple criteria in Excel using the example below; Explanation To lookup and approximate match based on more than one criteria, you can use an array formula based on INDEX and MATCH, with help from the IF function. In the example shown, the formula in G8 is: …

Continue Reading

Count rows with at least n matching values

by

This tutorial shows how to Count rows with at least n matching values using the example below; Formula {=SUM(–(MMULT(–(criteria),TRANSPOSE(COLUMN(data)^0))>=N))} Explanation To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in K6 is: {=SUM(–(MMULT(–((data)<70),TRANSPOSE(COLUMN(data)^0))>=2))} where data is the named range C5:I14. Note this …

Continue Reading

Count rows that contain specific values in Excel

by

This tutorial shows how to Count rows that contain specific values in Excel using the example below; Formula =SUM(–(MMULT(–(criteria),TRANSPOSE(COLUMN(data)))>0)) Explanation To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is: {=SUM(–(MMULT(–(data=90),TRANSPOSE(COLUMN(data)))>0))} where data is the named range B4:B12. Note: this …

Continue Reading

Offset in Excel

by

The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. OFFSET function Syntax; OFFSET(reference, rows, cols, [height], [width]) 1. The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The …

Continue Reading

Vlookup Examples in Excel

by

The VLOOKUP function is one of the most popular functions in Excel. This chapter contains many easy to follow VLOOKUP examples for Exact Match, Approximate Match, Right Lookup, First Match, Case-insensitive, Multiple Criteria, #N/A error  and  Multiple Lookup Tables. Exact Match Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. …

Continue Reading

INDEX function: Description, Usage, Syntax, Examples and Explanation

by

What is INDEX function in Excel? INDEX function is one of Lookup and Reference functions in Microsoft Excel that returns a value or the reference to a value from within a table or range. There are two ways to use the INDEX function: If you want to return a reference to specified cells, Click to see Reference …

Continue Reading

CHOOSE function: Description, Usage, Syntax, Examples and Explanation

by

What is Excel CHOOSE function? CHOOSE function is one of Lookup and Reference functions in Microsoft Excel that uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE …

Continue Reading

MATCH function: Description, Usage, Syntax, Examples and Explanation

by

What is MATCH function in Excel? MATCH function is one of Lookup and Reference functions in Microsoft Excel that is used to search for an item in a range of cells, and then return the relative position of that item in the range. Syntax of MATCH function MATCH(lookup_value, lookup_array, [match_type]) MATCH formula explanation The MATCH function syntax …

Continue Reading

VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

by

What is VLOOKUP function in Excel? VLOOKUP function is one of the lookup and reference functions in Microsoft office Excel that is used when you need to find things in a table or a range by row. For instance, look up a price of an automotive part by the part number. In its simplest form, the VLOOKUP function …

Continue Reading

LOOKUP function: Description, Usage, Syntax, Examples and Explanation

by

What is Excel LOOKUP function ? LOOKUP function  is one of the lookup and reference functions in Microsoft office Excel that is used to return a value from a range (one row or one column) or from an array. When you need to look in a single row or column and find a value from the same position …

Continue Reading

How to get last row in mixed data with blanks in Excel

by

To get the last relative position (i.e. last row, last column) for mixed data that may contain empty cells, you can use the MATCH function as described below. Note: this is an array formula and must be entered with Control+Shift+Enter. Formula {=MATCH(2,1/(range<>””))} Explanation In the example shown, the formula in E5 is: {=MATCH(2,1/(B4:B10<>””))} Last *relative* …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel TRUE Function
  • SWITCH function example in Excel
  • How to use Excel NOT Function
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Extract time from a date and time in Excel
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Display the current date in Excel
  • How to calculate future date say 6 months ahead in Excel
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers at uneven intervals in Excel
  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel

General

  • Count cells that contain errors in Excel
  • Flash Fill in Excel
  • How to generate random date between two dates in Excel
  • How to set or clear a print area in Excel Worksheet
  • Convert column number to letter in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning