# Understanding VlookUp(Rows

Frank Freese used Ask the Experts™
on
Folks,
Could someone please interpret for me why when using Vlookup() in combination with Row() one gets a sorted ranking list as seen in the example below:
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel VBA Developer
Top Expert 2014
Commented:
The VLOOKUP function is looking for a number in the Range B30:B39.  The ROWS function produces the number of rows in a defined range.

As the VLOOKUP function is copied down, the ROWS function produces a result that increases incrementally from 1 to 10 because the first part of the range is anchored (\$B\$30) while the second part of the range is changed relatively.  So, the formula on the tenth row looks like this:
=VLOOKUP(ROWS(\$B\$30:B39),\$B\$30:\$C\$39,2,FALSE)
and the ROWS function returns a value of 10.  That is the value that the VLOOKUP looks for in column B.

-Glenn

Commented:
AS I understand what you're saying it reorders based upon the ranking it found in column B?
Excel VBA Developer
Top Expert 2014
Commented:
Yes, because the values returned by the ROWS() function are naturally increasing as the range increases (i.e., 1, 2, 3..., 10).

You could visualize this better with a blank workbook.
Insert this formula in cell A1:
=ROWS(\$B\$1:B1)
Then copy it down a few rows.

BTW, the column letter doesn't really matter; it's the row range that's important.

Commented:
That explains it - haven't see  that before so you done educated me once again. Thank you Glenn

Commented:
Great job of explaining - EE have the best.
Thank you once again.
Excel VBA Developer
Top Expert 2014

Commented:
You're welcome.  The ROWS() function used this way is very handy for iterative calculations, especially in array functions where one wants to test a range of possible results (i.e., test for 1,2,3,4,5, and so on).

Do more with