## Get the latest revision

*Article last updated on February 09, 2018*

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents in alphanumeric format.

The array formula in cell F3 returns the latest revision based on the document name in cell F2.

The issue here is that the revisions may contain both letters and numbers and Excel can't extract the latest revision based on sorting from A to Z, that is why the revision hierarchy is in column H to guide Excel.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining array formula in cell F3

The IF function extracts revisions based on the document name in cell F2.

IF(F2=$B$3:$B$12, $C$3:$C$12, "")

becomes IF("SH-W2A2A"={"SH-W1A2A"; "SH-W2A2A"; "SH-W1A2B"; "SH-W1A2A"; "SH-W1A2A"; "SH-W1A2B"; "SH-W2A2A"; "SH-W2A2A"; "SH-W1A2B"; "SH-W1A2B"},{"650"; "24b"; "489"; "12y"; "759"; "698"; "18n"; "5k"; "116"; "Ca"},"")

and returns {"";"24b";"";"";"";"";"18n";"5k";"";""}.

The MATCH function then finds the position of each value in the array, if a value is not found the function returns an #N/A error.

MATCH(IF(F2=$B$3:$B$12, $C$3:$C$12, ""), $H$3:$H$2456,0)

becomes

MATCH({"";"24b";"";"";"";"";"18n";"5k";"";""}, $H$3:$H$2456,0)

and returns {#N/A; 1326; #N/A; #N/A; #N/A; #N/A; 1176; 822; #N/A; #N/A}

A higher number means a later revision.

The IFERROR function converts error values to blanks.

IFERROR(MATCH(IF(F2=$B$3:$B$12, $C$3:$C$12, ""), $H$3:$H$2456,0), "")

becomes

IFERROR({#N/A; 1326; #N/A; #N/A; #N/A; #N/A; 1176; 822; #N/A; #N/A}, "")

and returns {""; 1326; ""; ""; ""; ""; 1176; 822; ""; ""}.

The MAX function gets the largest number in the array, it corresponds to the latest revision.

MAX(IFERROR(MATCH(IF(F2=$B$3:$B$12, $C$3:$C$12, ""), $H$3:$H$2456, 0), ""))

becomes

MAX({""; 1326; ""; ""; ""; ""; 1176; 822; ""; ""})

and returns 1326.

The INDEX function then returns the latest revision value.

INDEX($H$3:$H$2456, MAX(IFERROR(MATCH(IF(F2=$B$3:$B$12, $C$3:$C$12, ""), $H$3:$H$2456,0), "")))

becomes

INDEX($H$3:$H$2456, 1326)

and returns 24b in cell F3.

### Download Excel *.xlsx file

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

Identify all characters in a cell value

Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Combine data from multiple sheets

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form