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

Data Analysis

  • Conditional Formatting Icon Sets Examples in Excel
  • Remove Duplicates Example in Excel
  • How to do a t-Test in Excel?
  • How to Sort by Color in Excel
  • How to create Checklist in Excel

References

  • How to get last row in mixed data with blanks in Excel
  • Multi-criteria lookup and transpose in Excel
  • To count total rows in a range in Excel
  • How to retrieve first match between two ranges in Excel
  • How to use Excel ROWS Function

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation whole percentage only
  • Excel Data validation date in specific year
  • Excel Data validation specific characters only
  • Excel Data validation require unique number

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 get last column number in range in Excel

by

You can get the last column in a range with a formula based on the COLUMN function. See example below: Formula =MIN(COLUMN(range))+COLUMNS(range)-1 Note: When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array …

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

Last row number in range

by

You can get the last row in a range with a formula based on the ROW function. See example below; Formula =MIN(ROW(range))+ROWS(range)-1 Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data))+ROWS(data)-1 where data is a named range for B5:D10 How this formula works When given a single cell reference, the ROW function …

Continue Reading

To count total rows in a range in Excel

by

If you need to count the number of rows in a range, use the ROWS function. The ROWS counts the number of rows in the supplied range and returns that number as the result. See illustration below: Formula =ROWS(range) Explanation In the example, the active cell contains this formula: =ROWS(B4:C9)

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

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

Create hyperlink with VLOOKUP in Excel

by

To create a hyperlink from a lookup, you can use the VLOOKUP function together with the HYPERLINK function. The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name. Formula =HYPERLINK(VLOOKUP(name,table,column,0),name) Explanation In the example shown, the formula in F5 is: =HYPERLINK(VLOOKUP(E5,link_table,2,0),E5) How this formula works Working from …

Continue Reading

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

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

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

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

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

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

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

Posts navigation

  • Previous
  • 1
  • 2

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 IFS function in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function

Date Time

  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Calculate series of dates by workdays in Excel
  • Generate series of dates by weekends in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • Display Days in month in Excel

Grouping

  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign data to groups in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to make excel worksheets print on one page?
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • Basic numeric sort formula in Excel
  • Count cells less than in Excel
  • Excel Default Templates
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning