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

Data Analysis

  • Excel Frequency Function Example
  • Subtotal function in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to count table rows in Excel

References

  • How to retrieve first match between two ranges in Excel
  • How to get first row number in range in Excel
  • Merge tables with VLOOKUP in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get relative row numbers in a range in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation specific characters only
  • Excel Data validation require unique number
  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel

Tag: MATCH function

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

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

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

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

Excel MAX, MATCH and ADDRESS function illustration

by

Locate Maximum Value in Excel This example teaches you how to find the cell address of the maximum value in a column. 1. First, we use the MAX function to find the maximum value in column A. 2. Second, we use the MATCH function to find the row number of the maximum value. Explanation: the MATCH function reduces to …

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

Match next highest value in Excel

by

This tutorial shows how to Match next highest value in Excel using the example below; Formula =INDEX(data,MATCH(lookup,values)+1) Explanation To match the “next highest” value in a lookup table, you can use a formula based on INDEX and MATCH. In the example shown, the formula in F6 is: =INDEX(level,MATCH(F4,points)+1) where “level” is the named range C5:C9, …

Continue Reading

Count cells not equal to many things in Excel

by

This tutorial shows how to Count cells not equal to many things in Excel using the example below; Formula =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) Explanation To count cells not equal to any of many things, you can use a formula based on the MATCH, ISNA, and SUMPRODUCT functions. In the example shown, the formula in cell F5 is: =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) …

Continue Reading

Match first error in Excel

by

This tutorial shows how to Match first error in Excel using the example below; Formula {=MATCH(TRUE,ISERROR(range),0)} Explanation If you need to match the first error in a range of cells, you can use an array formula based on the MATCH and ISERROR functions. In the example shown, the formula is: {=MATCH(TRUE,ISERROR(B4:B11),0)} This is an array …

Continue Reading

Count unique text values in a range in Excel

by

This tutorial shows how to Count unique text values in a range in Excel using the example below; Formula =SUMPRODUCT(–(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0)) Explanation If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT. It’s also possible to use COUNTIF, as explained …

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

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

Get first match cell contains in Excel

by

This tutorial shows how to Get first match cell contains in Excel using the example below; Formula {=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))} Explanation To check a cell for one of several things, and return the first match found in the list, you can use an INDEX / MATCH formula that uses SEARCH or FIND to locate a match. This …

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

SUMPRODUCT count multiple OR criteria in Excel

by

This tutorial shows how to work SUMPRODUCT count multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(ISNUMBER(MATCH(range1,{“A”,”B”},0))*ISNUMBER(MATCH(range2,{“X”,”Y”,”Z”},0))) Explanation To count matching rows with multiple OR criteria, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in F10 is: =SUMPRODUCT(ISNUMBER(MATCH(B5:B11,{“A”,”B”},0))* ISNUMBER(MATCH(C5:C11,{“X”,”Y”,”Z”},0))) This formula returns a count of rows …

Continue Reading

Get first non-blank value in a list in Excel

by

This tutorial shows how to Get first non-blank value in a list in Excel using the example below; Formula {=INDEX(range,MATCH(FALSE,ISBLANK(range),0))} Explanation If you need to get the first non-blank value (text or number) in a in a one-column range you can use an array formula based on the INDEX, MATCH, and ISBLANK functions. In the …

Continue Reading

Next largest match with the MATCH function in Excel

by

This tutorial shows how to  calculate Next largest match with the MATCH function in Excel using the example below; Formula =MATCH(value,array,-1) Explanation To lookup the “next largest” match in a set of values, you can use the MATCH function in approximate match mode, with -1 for match type. In the example shown, the formula in F7 is: …

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

Get information corresponding to max value in Excel

by

This tutorial shows how to Get information corresponding to max value in Excel using the example below; Formula =INDEX(range1,MATCH(MAX(range2),range2,0) Explanation To lookup information related to the maximum value in a range, you can use a formula that combines the MAX, INDEX, and MATCH functions. In the example shown, the formula in I8 is: =INDEX(F3:F11,MATCH(MAX(C3:C11),C3:C11,0)) Which …

Continue Reading

Partial match against numbers with wildcard in Excel

by

This tutorial shows how to calculate Partial match against numbers with wildcard in Excel using the example below; Formula {=MATCH(“*”&number&”*”,TEXT(range,”0″),0)} Explanation To perform a partial match (a substring match) against numbers, you can use an array formula based on MATCH and TEXT. Background Excel supports the wildcard characters “*” and “?”. However, if you use wildcards with a number, you’ll …

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

Position of first partial match in Excel

by

This tutorial shows how to  work Position of first partial match using the example below; Formula =MATCH(“*text*”,range,0) Explanation To get the position of the first partial match (i.e. the cell that contains text you are looking for) you can use the MATCH function with wildcards. In the example shown, the formula in E8 is: =MATCH(“*”&E7&”*”,B6:B11,0) How this formula works …

Continue Reading

Basic INDEX MATCH exact in Excel

by

This tutorial shows how to calculate Basic INDEX MATCH exact in Excel using the example below; Formula =INDEX(data,MATCH(value,lookup_column,FALSE),column) Explanation This example shows how to use INDEX and MATCH to get information from a table based on an exact match. In the example shown, the formula in cell H6 is: =INDEX(B5:E9,MATCH(H4,B5:B9,FALSE),2) which returns 1995, the year …

Continue Reading

Position of max value in list in Excel

by

This tutorial shows how to  calculate Position of max value in list in Excel  using the example below; Formula =MATCH(MAX(range),range,0) Explanation To get the position of the maximum value in a range (i.e. a list, table, or row), you can use the MAX function together with the MATCH function. In the example shown, the formula in …

Continue Reading

Case sensitive match in Excel

by

This tutorial shows how to calculate Case sensitive match in Excel using the example below; Formula {=MATCH(TRUE,EXACT(range,value),0)} Explanation To perform a case sensitive match, you can use the EXACT function together with MATCH in an array formula. In the example show, the formula in E8 is: {=MATCH(TRUE,EXACT(B5:B11,E6),0)} Note: this is an array formula and must …

Continue Reading

Count missing values in Excel

by

This tutorial shows how to calculate Count missing values in Excel using the example below; Formula =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Explanation To count the values in one list that are missing from another list, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in H6 is: =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Which returns …

Continue Reading

Lookup lowest value in Excel

by

This tutorial shows how to Lookup lowest value in Excel using the example below; Formula =INDEX(range,MATCH(MIN(vals),vals,0)) Explanation To lookup information associated with the lowest value in table, you can use a formula based on INDEX, MATCH, and MIN functions. In the example shown, a formula is used to identify the name of the contractor with …

Continue Reading

Match first occurrence does not contain

by

This tutorial shows how to Match first occurrence does not contain in Excel using the example below; Formula {=MATCH(FALSE,logical_test,0)} Explanation To get the position of the first match that does not contain a specific value, you can use an array formula based on the MATCH, SEARCH, and ISNUMBER functions. In the example shown, the formula in E5 is: …

Continue Reading

Excel VLOOKUP and HLOOKUP functions Examples

by

Learn all about Excel’s lookup & reference functions such as the VLOOKUP, HLOOKUP, CHOOSE, MATCH and INDEX  function. Navigation: Formula Tab → Function Library Group → Lookup and Reference Vlookup VLOOKUP stands for Vertical lookup.  The VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column …

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

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

How to retrieve first match between two ranges in Excel

by

To retrieve the first match in two ranges of values, you can use a formula based on the INDEX, MATCH, and COUNTIF functions. Formula =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) Explanation In the example shown, the formula in G5 is: =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) where “range1” is the named range B5:B8, “range2” is the named range D5:D7. How this formula works In this example the named range …

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

How to get last row in numeric data in Excel

by

To get the last relative position (i.e. last row, last column) for numeric data (with or without empty cells), you can use the MATCH function with a so called “big number”. see example below: Formula =MATCH(bignum,range) Explanation In the example shown, the formula in E5 is: =MATCH(9.99E+307,B4:B9) Last *relative* position, not row on worksheet When …

Continue Reading

How to get last row in text data in Excel

by

To get the last relative position (i.e. last row, last column) for text data (with or without empty cells), you can use the MATCH function. See example below: Formula =MATCH(bigtext,range) Explanation In the example shown, the formula in E5 is: =MATCH(REPT(“z”,255),B4:B11) How this formula works This formula uses the MATCH function in approximate match mode to locate …

Continue Reading

Check if range contains a value not in another range in Excel

by

To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, …

Continue Reading

Example of COUNTIFS with variable table column in Excel

by

To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. See example below: Formula =COUNTIFS(INDEX(Table,0,MATCH(name,Table[#Headers],0)),criteria)) Explanation In the example shown, the formula in H5 is: =COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0)),”x”) How this formula works First, for context, it’s important to note that you can use COUNTIFS with …

Continue Reading

Get column index in Excel Table

by

To get the index of a column in an Excel Table, you can use the MATCH function. See example below: Formula =MATCH(name,Table[#Headers],0) Explanation In the example shown, the formula in I4 is: =MATCH(H4,Table1[#Headers],0) When the formula is copied down, it returns an index for each column listed in column H. Getting an index like this …

Continue Reading

How to calculate two-way lookup VLOOKUP in Excel Table

by

To do a two-way lookup in an Excel Table, you can use the MATCH function with a structured reference and VLOOKUP. See example below: Recall that VLOOKUP depends on the lookup value being to the left of the value being retrieved in a table. Generally, this means the lookup value will be the first value in the table. …

Continue Reading

Basic text sort formula in Excel

by

To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<=”&A1) Explanation In the example shown, the formula in C5 is: =COUNTIF(countries,”<=”&B5) where “countries” is the named range B4:B13 How this formula works This formula uses the “greater than or equal to” operator with text, something you …

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

  • Excel If, Nested If, And/Or Criteria Examples
  • How to use IFS function in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel

Date Time

  • Check If Two Dates are same month in Excel
  • Count day of week between dates in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get week number from date in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers at uneven intervals in Excel
  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel

General

  • How to password protect excel sheet?
  • How to make excel worksheets print on one page?
  • How to fill cell ranges with random number from fixed set of options in Excel
  • Convert column letter to number in Excel
  • How to calculate percentage discount in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning