How to use vlookup to return the last value in a table?

Question

For example, my table which I will call Table1 (I have defined this as a name) is added to as new data comes in. How can I use vlookup to retrieve the 2nd column value of the final (last) data point at the bottom of Table1?

Answer 1

Is the first column numerical or text? You can use the range lookup option and search for a value that is going to be greater than any value in your table. If your first column contains words, try:

=VLOOKUP("ZZZZZ", Table1, 2, TRUE)

or, if your first column is a five-digit number, try:

=VLOOKUP("99999", Table1, 2, TRUE)

And it just occurred to me that this will work too, but is slower. Define the first column of Table1 as Table1Col1.

=VLOOKUP(MAX(Table1Col1), Table1, 2, TRUE)

Answer 2

If you want to dynamically update the column you're selecting you can use a MATCH() formula to fill in the col_index_num value. For example:

=VLOOKUP("d", Table1, 2, FALSE)

will return the value in the second column. whereas:

=VLOOKUP("d",Table1[#All],MATCH("value2",Table1[#Headers],0),FALSE)

will return the value in the column whose header is value2. Even more dynamically, you can write it like this:

=VLOOKUP("d", Table1, MATCH(Table1[[#Headers],[value2]],Table1[#Headers],0), FALSE)

which will update automagically if you rename the table column headers

Answer 3

=VLOOKUP(A2,Table1,2,MAX(Table1Col2))

Explanation: =VLOOKUP(**Data being looked up**,**Name of Table or data range**,**column of data being looked up**,MAX(**Name or data range of data being looked up**))

It will bring back the last value of the rows which match the lookup_value