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.
Download Excel *.xlsx file
Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]
How to perform a two-dimensional lookup
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
Find last matching value in an unsorted list
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]
Lookup with any number of criteria
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
How to enter array formulas in merged cells
Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
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.