Understanding VlookUp(Rows

Frank Freese
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:
Vlookup and rows
Comment
Watch Question

Do more with

Expert Office
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

Author

Commented:
AS I understand what you're saying it reorders based upon the ranking it found in column B?
Glenn RayExcel 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.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

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

Author

Commented:
Great job of explaining - EE have the best.
Thank you once again.
Glenn RayExcel 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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial