Forum Discussion

PedroDerek's avatar
PedroDerek
Copper Contributor
May 17, 2024

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!

 

 

 

 

  • PedroDerek 

    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)

    • PedroDerek's avatar
      PedroDerek
      Copper Contributor
      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!
  • PedroDerek 

    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.