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 text data in Excel
  • How to get last row in mixed data with blanks in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • To count total rows in a range in Excel
  • How to get first column number in range in Excel

Data Analysis Examples

  • How to perform a t-Test in Excel
  • How to calculate average last N values in a table in Excel
  • How to create dynamic reference table name in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to count table rows in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation number multiple 100
  • Excel Data validation don’t exceed total
  • Excel Data validation whole percentage only
  • Excel Data validation allow weekday only

Lookup last file version in Excel

by

This tutorial shows how to Lookup last file version in Excel using the example below;

Formula

=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Explanation

To lookup the latest file version in a list, you can use a formula based on the LOOKUP function together with the ISNUMBER and FIND functions. In the example shown, the formula in cell G7 is:

=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)

where “files” is the named range B5:B11.

Context

In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated with a counter at the end as a revision number – 001, 002, 003, etc.

Given a file name, we want to retrieve the name of the last or latest revision. There are two challenges:

  1. The challenge is the version codes at the end of the file names make it harder to match the file name.
  2. By default, Excel match formulas will return the first match, not the last match.
Worked Example:   How to count keywords in a range of cell

To overcome these challenges, we need to use some tricky techniques.

How this formula works

This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this:

1/(ISNUMBER(FIND(G6,files)))

Inside this snippet, the FIND function looks for the value in G6 inside the named range “files” (B5:B11). The result is an array like this:

{1;#VALUE!;1;1;#VALUE!;#VALUE!;1}

Here, the number 1 represents a match, and the #VALUE error represents a non-matching file name. This array goes into the ISNUMBER function and comes out like this:

{TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE}

Error values are now FALSE, and the number 1 is now TRUE. This overcomes challenge #1, we now have an array that shows clearly which files in the list contain the file name of interest.

Worked Example:   Excel Data validation must not contain

Next, the array is used as the denominator with 1 as numerator. The result looks like this:

{1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1}

which goes into LOOKUP as the lookup_vector. This is a tricky solution to challenge #2. The LOOKUP function operates in approximate match mode only, and automatically ignores error values. This means with 2 as a lookup value, VLOOKUP will try to find 2, fail, and step back to the previous number (in this case matching the last 1 in position 7). Finally, LOOKUP uses 7 like an index to retrieve the 7th file in the list of files.

Worked Example:   Get first match cell contains in Excel

Handling blank lookups

Oddly, the FIND function returns 1 if the lookup value is an empty string (“”). To guard against a false match, you can wrap the formula in IF and test for an empty lookup:

=IF(G6<>"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")

 

Post navigation

Previous Post:

Lookup entire row in Excel

Next Post:

Lookup latest price in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

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
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • How to Capitalize first letter in a sentence in Excel
  • How to count total characters in a range in Excel
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • Find Last Day of the Month in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Assign points based on late time in Excel
  • Get day name from date in Excel
  • Sum through n months in Excel
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles