Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Understanding VlookUp(Rows

Posted on 2014-09-24
6
Medium Priority
?
117 Views
Last Modified: 2014-09-25
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
0
Comment
Question by:Frank Freese
  • 3
  • 3
6 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40343240
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
 

Author Comment

by:Frank Freese
ID: 40343965
AS I understand what you're saying it reorders based upon the ranking it found in column B?
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 2000 total points
ID: 40343978
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Frank Freese
ID: 40344002
That explains it - haven't see  that before so you done educated me once again. Thank you Glenn
0
 

Author Closing Comment

by:Frank Freese
ID: 40344004
Great job of explaining - EE have the best.
Thank you once again.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40344257
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question