Different Methods in Excel to Lookup Last Value in Column

Today we are going to discuss about numerous methods in Excel to do Lookup and to find the last value in column. Lot to learn, lets dive in.

Source data resides in the range B3:D14, which monitors the department change of each employee in an organization.

We can see 3 entries for Employee ID E1011, and his/her recent assignment is in Design department.

Our requirement is to find the last matching 'Name' and 'Department' for the given 'Emp ID' which is in F4 cell.

Method-1:

Simple Construction in Office 365 version,

Formula in G4 cell is,

=TAKE(FILTER(C4:D14,B4:B14=F4),-1)

Let me start this from scratch.

=FILTER(C4:D14,B4:B14=F4)

Our requirement is to get Emp Name and Department, so we apply filter only for that range C4:D14. In include argument, we need to feed Boolean values of TRUE and FALSE, in which matching records of TRUE will be delivered by FILTER function.

In include argument,

We need the records of 'E1012', so

=B4:B14=F4

will deliver us,

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

2nd and 7th records are the records of Emp ID E1012.

So, FILTER function will deliver the matching records as shown below,

{"Leila","Business Intelligence";"Leila","Market Intelligence"}

TAKE function helps us to get the last record.

Method-2:

Requires Excel 2021 version.

Formula in G4 cell is,

=LET(All,FILTER(C4:D14,B4:B14=F4),INDEX(All,ROWS(All),))

'All' is the variable name or container to hold all matching records of given criteria.

We already discussed about the construction which involves FILTER function in Method-1.

If we take Emp ID E1011,

FILTER function will deliver us 3 records of 'Nabil'.

{"Nabil","Production";"Nabil","R & D";"Nabil","Design"}

To get the last record, we need to get the count of all records. ROWS function will help us to get the count of all matching records which is 3.

We can use this count as row_num argument in INDEX function to extract last record.

Method-3:

Works in all versions but requires CTRL + SHIFT + ENTER in Excel 2016 or earlier version.

Formula in G4 cell is,

=INDEX(C$4:C$14,MAX(($B$4:$B$14=$F4)*(ROW($B$4:$B$14)-ROW($B$4)+1)))

Instead of Enter press CTRL + SHIFT + ENTER and Drag the formula across column to H4 cell.

Let start this from scratch,

=($B$4:$B$14=$F4)

this will deliver us Boolean values as shown below,

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

=(ROW($B$4:$B$14)-ROW($B$4)+1)

This will help us to get the sequence of numbers from 1 to the number based on number of rows, in our case it is 11. So, it will deliver us the sequence of numbers from 1 to 11.

{1;2;3;4;5;6;7;8;9;10;11}

If we Multiply the Boolean values with sequence of numbers, then we will get

{0;2;0;0;0;0;7;0;0;0;0}

Now Max function will help us to get maximum which is 7.

Then INDEX function will help us to get 7th item in C4:C14 range which is 'Leila'.

In H4 cell C$4:C$14 will become D$4:D$14. So, 7th item in that range is

'Market Intelligence'.