Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

You’ll sometimes need to know the relative position, such as the relative row in a list or table of an item. This is usually different than the item’s actual row on the Excel spreadsheet grid.

For example, suppose you have a table of data such as that pictured below, where you want to know the row position in that table of the maximum annual expense item. As you see, the expense item is Payroll because its annual number is the largest in range F5:F15. True, the maximum number in the list resides on row 12 of the spreadsheet, but what you need to know is that maximum number’s position, relative to the range of interest.

In cell H2, the formula =MATCH(MAX(F5:F15),F5:F15,0) returns 8 because the 8th row (position) in the range of F5:F15 holds the maximum number.