CODEWITHSUNDEEP
excel
Ben
Ben

Reputation: 1011

Return the last value from an array

I have some data in one single excel sheet - there are multiple sets of data, each with the same number of columns (pretend 2 columns). I will always be adding rows to these "tables" and I would like some way to always get the last value in the table by looking up the name of bank by using a formula. For example:

Here is the top of the excel document with labels on the left and then a formula which is going to lookup the label and then return the value that is in the last row. I can do this if I always update the hard-coded references in the VLOOKUP formula but I would like a way to always refer to the last cell in the data set.

Bank 1: =lookup value (Bank1) and fetch $7
Bank 2: =lookup value (Bank2) and fetch $25

Here is an example of the arrays I am referring to:

Bank1   $10
Bank1   $15
Bank1   $7


Bank2  $15
Bank2  $18
Bank2  $25

Upvotes: 0

Views: 1249

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

If the banks were always in alphabetical order you could use an INDEX/MATCH combination. But assuming there not, I'd use a SUMPRODUCT formula. Assuming a layout like:

enter image description here

Put this in E2 and copy down:

=INDEX($B$2:$B$1000,SUMPRODUCT(MAX(($A$2:$A$1000=D2)*ROW($A$2:$A$1000))-1))

This assumes your data extends to row 1000.

Upvotes: 2

Related Questions