Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel COLUMN Function
  • Find closest match in Excel
  • How to use Excel MATCH Function

Data Analysis Examples

  • How To Sort One Column or Multiple Columns in Excel
  • Error Bars in Excel
  • Randomize/ Shuffle List in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Conditional Formatting Rules in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation allow weekday only
  • Excel Data validation exists in list
  • Excel Data validation no punctuation
  • Prevent invalid data entering in specific cells

Tag: MATCH function

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

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

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

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

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

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

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 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 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 generate random number weighted probability in Excel

by

To generated a random number, weighted with a given probability, you can use a helper table together with a formula based on the RAND and MATCH functions. Formula =MATCH(RAND(),cumulative_probability) Explanation In the example shown, the formula in F5 is: =MATCH(RAND(),D$5:D$10) How this formula works This formula relies on the helper table visible in the range …

Continue Reading

Find most frequent text within a range with criteria in Excel

by

To find the most frequently occurring text in a range, based on criteria you supply, you can use an array formula based on several Excel functions MATCH, MODE, INDEX,  and IF. Formula =INDEX(range1,MODE(IF(range2=criteria, MATCH(range1,range1,0)))) Note: this is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula …

Continue Reading

Extract most frequently occurring text in Excel

by

To extract the word or text value that occurs most frequently in a range, you can use a formula based on several functions INDEX, MATCH, and MODE. Formula =INDEX(range,MODE(MATCH(range,range,0))) Explanation In the example shown, the formula in H5 is: =INDEX(B5:F5,MODE(MATCH(B5:F5,B5:F5,0))) Working from the inside out, the MATCH function matches the range against itself. That is, …

Continue Reading

How to calculate next scheduled event in Excel

by

To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH  to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control + …

Continue Reading

Get work hours between dates custom schedule in Excel

by

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. Formula =SUMPRODUCT(MID(schedule,WEEKDAY (ROW(INDIRECT(start&”:”&end))),1)*ISNA (MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0))) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT(MID(D6,WEEKDAY(ROW (INDIRECT(B6&”:”&C6))),1)*ISNA(MATCH (ROW(INDIRECT(B6&”:”&C6)),holidays,0))) Which returns 36 hours, based on a custom …

Continue Reading

How to abbreviate names or words in Excel

by

To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms. Only capital letters will survive this formula, so the source text must include capitalized words. You can …

Continue Reading

Excel Data validation specific characters only

by

Set criteria to accept specific characters only To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. Formula =COUNT(MATCH(MID(A1,ROW(INDIRECT (“1:”&LEN(A1))),1),allowed&””,0))=LEN(A1) Explanation In the example shown, data validation is applied with this formula: =COUNT(MATCH(MID(B5,ROW(INDIRECT (“1:”&LEN(B5))),1),allowed&””,0))=LEN(B5) where “allowed” is …

Continue Reading

Highlight approximate match lookup conditional formatting in Excel

by

This tutorial shows how to Highlight approximate match lookup conditional formatting in Excel using the example below; Formula =OR($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights)) Explanation To highlight rows and columns associated with an approximate match, you can use conditional formatting with a formula based on the LOOKUP function together with with a logical function like OR or AND. In the example …

Continue Reading

Categorize text with keywords in Excel

by

This tutorial shows how to Categorize text with keywords in Excel using the example below; Formula {=INDEX(categories,MATCH(TRUE,ISNUMBER (SEARCH(keywords,text)),0))} Explanation To categorize text using keywords with a “contains” match, you can use the SEARCH function, with help from INDEX and MATCH. In the example shown, the formula in C5 is: {=INDEX(categories,MATCH(TRUE, ISNUMBER(SEARCH(keywords,B5)),0))} where “keywords” is the named …

Continue Reading

Two-way lookup with INDEX and MATCH in Excel

by

This tutorial shows how to calculate Two-way lookup with INDEX and MATCH in Excel using the example below; Formula =INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1)) Explanation To lookup in value in a table using both rows and columns, you can build a formula that does a two-way lookup with INDEX and MATCH. In the example shown, the formula in J8 …

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

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

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

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

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

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

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

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

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

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

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

INDEX and MATCH with multiple criteria in Excel

by

This tutorial shows how to calculate INDEX and MATCH with multiple criteria in Excel using the example below; Formula {=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))} Explanation To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. In the example shown, the formula in H8 is: {=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))} Note: this is an array formula, and must be …

Continue Reading

Index and match on multiple columns in Excel

by

This tutorial shows how to calculate Index and match on multiple columns in Excel  using the example below; Formula {=INDEX(range1,MATCH(1,MMULT(–(range2=critera), TRANSPOSE(COLUMN(range2)^0)),0))} Explanation To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(–(names=G4), TRANSPOSE(COLUMN(names)^0)),0))} where …

Continue Reading

Join tables with INDEX and MATCH in Excel

by

This tutorial shows how to Join tables with INDEX and MATCH in Excel using the example below; Formula =INDEX(data,MATCH(lookup,ids,0),2) Explanation To join or merge tables that have a common id, you can use the INDEX and MATCH functions. In the example shown, the formula in E5 is: =INDEX(data,MATCH($C5,ids,0),2) where “data” is the named range H5:J8 and “ids” …

Continue Reading

INDEX and MATCH descending order in Excel

by

This tutorial shows how to calculate INDEX and MATCH descending order in Excel using the example below; Formula =INDEX(range1,MATCH(lookup,range2,-1)) Explanation To retrieve values from a table where lookup values are sorted in descending order [Z-A] you can use INDEX and MATCH, with MATCH configured for approximate match using a match type of -1. In the example …

Continue Reading

Get nth match with VLOOKUP in Excel

by

This tutorial shows how to Get nth match with VLOOKUP in Excel  using the example below; Formula =VLOOKUP(id_formula,table,4,0) Explanation To get the nth MATCH with VLOOKUP, you’ll need to add a helper column to your table that constructs a unique id that includes the count. If this isn’t practical, you can use an array formula based …

Continue Reading

Lookup entire column in Excel

by

This tutorial shows how to Lookup entire column in Excel  using the example below; Formula =INDEX(data,0,MATCH(value,headers,0)) Explanation To lookup and retrieve an entire column, you can use a formula based on the INDEX and MATCH functions. In the example shown, the formula used to lookup all Q3 results is: =INDEX(C5:F8,0,MATCH(I5,C4:F4,0)) Note: this formula is embedded …

Continue Reading

Lookup and sum column in Excel

by

This tutorial shows how to Lookup and sum column in Excel  using the example below; Formula =SUM(INDEX(data,0,MATCH(val,header,0))) Explanation To lookup and return the sum of a column, you can use the a formula based on the INDEX, MATCH and SUM functions. In the example shown, the formula in I7 is: =SUM(INDEX(C5:F11,0,MATCH(I6,C4:F4,0))) How this formula works …

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

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
  • How to convert text string to array in Excel
  • Remove first character in a cell in Excel
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • Extract last name from full name — Manipulating NAMES in Excel
  • How to split text with delimiter in Excel
  • Calculate number of hours between two times in Excel
  • Calculate total hours that fall between two times in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Roll back weekday to Friday base on a particular date in Excel
  • How to get year from date in Excel
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles