Solved

Understanding VlookUp(Rows

Posted on 2014-09-24
6
86 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 500 total points
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Frank Freese
Comment Utility
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
Comment Utility
Great job of explaining - EE have the best.
Thank you once again.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now