Excel Equation: Pull the last entry for a specific item in a list

Problem

Someone at the winery just hit me up for some help on a periodic report that normally takes him about 2 hours to complete. He has to pull all the most recent values of a lab test for each new batch of wine. The batches are all assigned numbers but the lab spreadsheets are large and take forever to comb through.

Below and attached is the equation that should make things a bit easier.

Solution

To pull the most recent entry of an item regardless if it is a number or text we will use the INDEX function.

INDEX(array, row_num, [column_num])

max-row-example

The index function assigns a number to each cell highlighted in the array. The row number and column number act like x and y variables that help point to the value you would like returned. It is a lot like playing battleship with a function.

In our example we have a table with a few key variables.

  • Item Description
  • Value

To return the last value in a given list we will use INDEX, MATCH,IF, and MAX functions in an array formula.

Here is the equation and attached is a spreadsheet example:

LINK TO FILE>>max-row-given-conditions

{INDEX=( [COLUMN of VALUES] ,MATCH( [SPECIFIC ITEM],IF((MAX(IF([LIST OF ITEMS]=[SPECIFIC ITEM],ROW([LIST OF ITEMS])))=ROW([LIST OF ITEMS])),[LIST OF ITEMS]),0))}

Leave a Reply

Your email address will not be published.