Author: Oscar Cronquist Article last updated on October 31, 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.

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

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.