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

Lookup and Reference Examples

  • How to get last row in mixed data with blanks in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Find closest match in Excel
  • How to get first column number in range in Excel
  • Extract all partial matches in Excel

Data Analysis Examples

  • Data Series in Excel
  • How to Create Column Chart in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Remove Duplicates Example in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only
  • Excel Data validation only dates between
  • Excel Data validation must contain specific text
  • Excel Data validation no punctuation

Category: Excel Functions

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 multiple matches into separate rows in Excel

by

This tutorial shows how to calculate Extract multiple matches into separate rows in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is: {=IFERROR(INDEX(names,SMALL (IF(groups=E$4,ROW(names)-MIN(ROW (names))+1),ROWS($E$5:E5))),””)} This is an …

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

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

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

Break ties with helper column and COUNTIF in Excel

by

This tutorial shows how to Break ties with helper column and COUNTIF in Excel using the example below; Formula =A1+(COUNTIF(exp_rng,A1)-1)*adjustment Explanation To break ties, you can use a helper column and the COUNTIF function to adjust values so that they don’t contain duplicates, and therefore won’t result in ties. In the example shown, the formula in D5 …

Continue Reading

Average the last 3 numeric values in Excel

by

This tutorial shows how to work  Average the last 3 numeric values in Excel using the example below; Formula {=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3}),ROW(data), data))} Explanation To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example …

Continue Reading

Count if two criteria match in Excel

by

This tutorial shows how to Count if two criteria match in Excel using the example below; Formula =COUNTIFS(range1,critera1,range2,critera2) Explanation If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function. In the example shown, we want to count the number of orders with a …

Continue Reading

Count matches between two columns in Excel

by

This tutorial shows how to Count matches between two columns in Excel using the example below; Formula =SUMPRODUCT(–(range1=range2)) Explanation If you want to compare two columns and count matches in corresponding rows, you can use the SUMPRODUCT function with a simple comparison of the two ranges. For example, if you have values in B5:B11 and …

Continue Reading

Count rows with multiple OR criteria in Excel

by

This tutorial shows how to Count rows with multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(–((criteria1)+(criteria2)>0)) Explanation To count rows using multiple criteria across different columns – with OR logic – you can use the SUMPRODUCT function. In the example shown, the formula in H7 is: =SUMPRODUCT(–((C5:C11=”blue”)+(D5:D11=”dog”)>0)) How this formula works In the example shown, we …

Continue Reading

Count total matches in two ranges in Excel

by

This tutorial shows how to Count total matches in two ranges in Excel using the example below; Formula =SUMPRODUCT(COUNTIF(range1,range2)) Explanation If you want to compare two ranges, and count total matches between the two ranges, you can use a formula that combines the COUNTIF and  SUMPRODUCT functions. Context Suppose you have a “master” list of some …

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

Count visible rows in a filtered list in Excel

by

This tutorial shows how to Count visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(3,range) Explanation If you want to count the number of visible items in a filtered list, you can use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter. The SUBTOTAL function can perform calculations …

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

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 visible rows only with criteria in Excel

by

This tutorial shows how to Count visible rows only with criteria in Excel using the example below; Formula =SUMPRODUCT((range=criteria)*(SUBTOTAL(3,OFFSET(range,rows,0,1)))) Explanation To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET. The problem The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden …

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

Sum bottom n values in Excel

by

This tutorial shows how to Sum bottom n values in Excel. You can use a combination of SUMPRODUCT function and SMALL function to get the sum bottom  n values in the example below; Formula =SUMPRODUCT(SMALL(range,{1,2,n})) Explanation If you need to sum or add the bottom values in a range, you can do so with a formula that uses the …

Continue Reading

Sum bottom n values with criteria in Excel

by

This tutorial shows how to Sum bottom n values with criteria in Excel. You can use a combination of SUM function, SMALL function and IF function to get the sum bottom in the example below; Formula {=SUM(SMALL(IF(range1=criteria,range2),{1,2,3,N}))} Explanation To sum the bottom n values in a range matching criteria, you can use an array formula based on the …

Continue Reading

Sum last n columns in Excel

by

This tutorial shows how to Sum last n columns in Excel using the example below; Formula =SUM(INDEX(data,0,COLUMNS(data)-(n-1)):INDEX(data,0,COLUMNS(data))) Explanation To sum the last n columns in a table of data (i.e. last 3 columns, last 4 columns, etc.) you can use a formula based on the INDEX function. In the example shown, the formula in K5: …

Continue Reading

Sum visible rows in a filtered list in Excel

by

This tutorial shows how to Sum visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(9,range) Explanation If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered out), you can use the SUBTOTAL function with function number 9 or 109. What makes SUBTOTAL …

Continue Reading

Sum through n months in Excel

by

This tutorial shows how to Sum through n months in Excel using the example below; Formula =SUM(OFFSET(start,0,0,N,1)) Explanation To sum a set of monthly data through n number of months, you can use a formula based on the SUM and OFFSET functions. In the example shown, the formula in G6 is: =SUM(OFFSET(C5,0,0,G5,1)) How this formula …

Continue Reading

How to use Excel FALSE Function

by

This Excel tutorial explains how to use the Excel FALSE function with syntax and examples. Excel FALSE Function Description The Excel False function returns the logical value FALSE. The function takes no arguments and therefore, the syntax is simply: FALSE() Note that you can also get the same result by simply typing the text ‘False’ into your …

Continue Reading

How to use Excel XOR Function

by

This Excel tutorial explains how to use the XOR function with syntax and examples. Excel XOR Function Function Description Excel XOR function performs what is called “exclusive OR”. The XOR function returns the Exclusive Or logical operation for one or more supplied conditions. With two logical statements, XOR returns TRUE if either statement is TRUE, but returns …

Continue Reading

How to use Excel TRUE Function

by

This Excel tutorial explains how to use the TRUE function with syntax and examples. Excel TRUE Function Description The Excel True function returns the logical value TRUE. The function takes no arguments and therefore, the syntax is simply: TRUE() Note that you can also get the same result by simply typing the text ‘True’ into …

Continue Reading

How to use Excel OR Function

by

This Excel tutorial explains how to use the  OR function with syntax and examples. Excel OR Function Description The Excel OR function tests a number of supplied conditions and  returns TRUE if any of the conditions are TRUE. Otherwise, it returns FALSE. That is; TRUE if ANY of the conditions evaluate to TRUE or FALSE otherwise (i.e. if ALL of the conditions …

Continue Reading

How to use Excel AND Function

by

This Excel tutorial explains how to use the AND function with syntax and examples. Excel AND Function Description Microsoft Excel AND function tests a number of supplied conditions and returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE. That is; TRUE if ALL of the conditions evaluate to TRUE or FALSE otherwise …

Continue Reading

How to use Excel NOT Function

by

This Excel tutorial explains how to use the NOT function with syntax and examples. Excel NOT Function Description The Excel NOT function returns the opposite to a supplied logical value. That is, If supplied with the value TRUE, the Not function returns FALSE; If supplied with the value FALSE, the Not function returns TRUE. Excel …

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 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

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 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 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 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

Posts navigation

  • Previous
  • 1
  • …
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 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 extract substring in Excel
  • CODE function: Description, Usage, Syntax, Examples and Explanation
  • Remove text by variable position in a cell in Excel
  • How to count specific words in a range in Excel
  • How to check if cell contains one of many things in Excel
  • Dynamic date list in Excel
  • Get work hours between dates in Excel
  • Add days to date in Excel
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Get date from day number in Excel
  • ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate original loan amount in Excel
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Bond valuation example in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles