# Problem with Autofill when Using VLOOKUP Function

Copper Contributor

# Problem with Autofill when Using VLOOKUP Function

Hello, I'm having a little problem when trying to drag and drop a formula to autofill with the VLOOKUP function. I have a long list and I'm using this function to display the highest sales in rank order. Not sure if this is the best way to do it, but it's how I first learned how to do it. So my formula is this:

=VLOOKUP(LARGE(\$C\$4:\$C\$10001,513),\$C\$4:\$E\$10001,3,FALSE).

What I would like for it to do is to have the 513 number to increment to 514 when I autofill to the next row. Then to 516, 517, etc. I have been manually changing this number the whole time, but it's a little tedious.

I have a similar problem with using this formula as well: =LARGE(\$C\$4:\$C\$1002,513). As stated above, I'd like the 513 rank number to adjust to 514 when I autofill the next line. Here is a screen shot that might help to see what I have. The highlighted yellow cell is my VLOOKUP function and the cell to the right is my LARGE function:

Any help with this would be greatly appreciated. Thank you!

3 Replies

# Re: Problem with Autofill when Using VLOOKUP Function

Since you already have the sequence number 513 in cell F516, you can use

=VLOOKUP(LARGE(\$C\$4:\$C\$10001,F516),\$C\$4:\$E\$10001,3,FALSE)

Alternatively, you could use

=VLOOKUP(LARGE(\$C\$4:\$C\$10001,ROW(G516)-3),\$C\$4:\$E\$10001,3,FALSE)

# Re: Problem with Autofill when Using VLOOKUP Function

Such a simple fix and this will be saving me so much time in the future! Thank you so much for your expertise and timely response!

# Re: Problem with Autofill when Using VLOOKUP Function

It looks as if you are trying to sort titles by earnings in descending order.  Also, from your choice of legacy functions, you are not using Excel 365?  Using 365 the result is given by a dynamic array formula in cell G4

``= SORT(HSTACK(Titles, Earnings), 2, -1)``

where HSTACK specifies the columns to return, 2 is the column to sort by, and -1 is the order.  If the input data is presented as an Excel Table the result array will grow as more data is added to the Table without user interaction.