# Get the latest revision

### Table of Contents

## 1. Get the latest revision

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.

### Get Excel *.xlsx file

## 2. Create a list with most recent data available

**Question:**i have two sets of data - one has an identifier column and one result column.A2 data1 B2 22

A3 data2 B3 55

A4 data3 B4 44

A5 data5 B5 22

A6 data4 B6 33

A7 data9 B7 22

A8 data6 B8 44

A9 data88 B9 55in the second set

D2 data1 E2 33

D3 data2 E3 55

D4 data3 E4 44

D5 data4 E5 33

D6 data88 E6 12

the new list should change B2 from 22 to 33.

If there is no change it shows the first sets result . if there is a change it reports from the second set.

To complicate it... the first set of data is twice as long as the second one. not all data is in the second set and finally the lists are not sorted and cant be.

**Formula in F3:**

copied down as far as needed.

### Explaining formula in cell B13

#### Step 1 - Count given value in cell range

The COUNTIF function counts cells based on a condition, it will return 1 if the value exists in cell range $B$13:$B$17.

COUNTIF($B$13:$B$17, E3)=0

becomes

COUNTIF({"data1";"data2";"data3";"data4";"data88"},"data1")=0

becomes

1=0 and returns FALSE.

#### Step 2 - Which cell range?

The IF function determines from which cell range the formula retrieves the value needed.

IF(COUNTIF($B$13:$B$17, E3)=0, INDEX($C$3:$C$10, MATCH(E3, $B$3:$B$10, 0)), INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0)))

becomes

IF(FALSE, INDEX($C$3:$C$10, MATCH(E3, $B$3:$B$10, 0)), INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0)))

and returns

INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0))

#### Step 3 - Get value

The MATCH function returns the relative position of a value in a cell range or array.

INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0))

becomes

INDEX($C$13:$C$17, 1)

The INDEX function returns a value based on a row number (and a column number if needed).

INDEX($C$13:$C$17, 1)

returns 33 in cell F3.

### Get Excel *.xlsx file

Create-a-column-with-most-recent-data-in-excel.xlsx

### Lookups category

This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]

Table of Contents How to perform a two-dimensional lookup Reverse two-way lookups in a cross reference table [Excel 2016] Reverse […]

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

### Misc category

This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]

This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]

### Excel categories

### 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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form