< Go Back

Lookup last file version

Looking up the last file version on Excel spreadsheet is pretty much easy. You could run into error only if you enter the formula in a wrong manner. Those who this lookup function are workshops and factories that need accurate sorting of their products models and files.

Syntax

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

How to Lookup Last File Version on Excel Spreadsheet

The Excel application could be used in various ways. One of the ways it could be used to perform a specific function is to act like a pseudo-database system where information can be called up frequently. Looking the last version of the file fits perfectly into that role.

How the Formula Works

This formula recognizes the LOOKUP function to get the last corresponding name of the file demanded name. In the example we will be using the lookup value is simply 2 and vector associated with the lookup is created in this format: 1/(ISNUMBER(FIND(G6,files)))

Embedded within that complicated look formula the find fiction is tagged G6 within the file name to be called up, and that translates to this: 1;#VALUE!;1;1;#VALUE!;#VALUE!;1}

Please note that the number in the above formula stands for a match. But whenever you see #VALUE, it basically means an error and the file requested cannot be found. Whenever the error values appear to be FALSE, there is an error. What comes after could translate to this {1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1}

Also, note that blank lookups are not something that should throw you off. To avoid errors as that, you can simply use your knowledge of the IF function and create your formula this way: =IF(G6<>””,LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),””)

Example

Getting a particular file name from your directory with the formula should be an easy one.

Step 1

Carefully enter your data in the cells on your spreadsheet as seen below.

Figure 1 shows data entered into appropriate cells.

Step 2

After all necessary data are correctly imputed, now select the cell in front of the cell labeled “Latest” and enter the formula.

Figure 2. Excel returns the last file version.

Notes

  • Do not make the common mistakes of increasing the sub-brackets than the required number.
  • When a sub-bracket is created, always ensure they are close so you do not run into errors.
  • Put the equal sign (=)  before your formula.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar