Understanding VlookUp(Rows

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:
Vlookup and rows
Frank FreeseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
AS I understand what you're saying it reorders based upon the ranking it found in column B?
0
Glenn RayExcel VBA DeveloperCommented:
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.
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Frank FreeseAuthor Commented:
That explains it - haven't see  that before so you done educated me once again. Thank you Glenn
0
Frank FreeseAuthor Commented:
Great job of explaining - EE have the best.
Thank you once again.
0
Glenn RayExcel VBA DeveloperCommented:
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).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.