Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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)
share|improve this answer
add comment

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

share|improve this answer
add comment
=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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.