LOOKUP file with last version

In this article, we will learn how to LOOKUP file with last version in Excel.

Let's first get some knowledge about the function.

LOOKUP function finds the estimated value in the range and returns the value from the range or its corresponding value.

Syntax:

= LOOKUP ( lookup_value, lookup_vector, [result_vector] )

lookup_value: value to look up for
lookup_vector: array where the function looks for the lookup_value.
[result vector]:[optional] if the return array is different from the lookup array.

But here we will be using its one of the attributes of the LOOKUP function i.e. if LOOKUP function can't find an exact match, it will match with just the previous value. It returns the last found value for the previous value. So we will be using the below syntax.
Generic Formula:

= LOOKUP ( 2 , FIND ( filename , range) , range )

Let’s understand this by using the above stated formula in an example

Here we have File versions in the first column and we need to find the last version for the fileA.

Use the formula in the D4 cell:

= LOOKUP ( 2 , FIND(D3 , range) , range)

Explanation:

  • If the filename match is found, the FIND function returns 1  for the matched values.
  • If the match is not found, then the FIND function returns the #VALUE error. And returns the array of 1's and #VALUE errors.
  • Now the lookup function lookup value ( 2 ) in the list of array of 1's and #VALUE! Errors.
  • So when the function doesn't find the value 2. So it looks backward for the value 1 ( just previous value of 2 ). 


Here the range is named range for the A2:A9 array.
Press Enter to get the results.

As you can see we got the last file version using the LOOKUP & FIND function.

Notes: 

  1. The formula returns #NA error if no match is found in the array.
  2. The formula checks the lookup value first and then looks up for the just previous value, if the lookup value is not found.
  3. All excel formula always returns the first value found in the table array.
  4. The 
  5. The above explained formula, lookup for the last file version in the filename array.  

Hope you understood how to use the LOOKUP function in Excel. Explore more articles on lookup value here. Please feel free to state your queries below in the comment box. We will certainly help you.

Related Articles

Use INDEX and MATCH to Lookup Value

SUM range with INDEX in Excel

How to use the SUM function in Excel

How to use the INDEX function in Excel

How to use the MATCH function in Excel

How to use LOOKUP function in Excel

How to use the VLOOKUP function in Excel

How to use the HLOOKUP function in Excel

Popular Articles

Edit a dropdown list

If with conditional formatting

If with wildcards

Vlookup by date

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube