Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel Function Problem

Posted on 2012-12-28
8
192 Views
Last Modified: 2013-09-21
Hello, I am looking for a function to do the following:

From Date: 12/01/2012 (let us say date is in C4)

               Column A           Column B
Row 10  11/30/2012         300,000
Row 11  11/30/2012         305,000
Row 12  12/01/2012         302,000
Row 13  12/01/2012         340,000
Row 14  12/01/2012         335,000

If C4, than return B11. I will have all the months on the spreadsheet; therefore, as I change C4 it should always return last transaction from the previous day. Data is always sorted the same way.

Thanks a lot.
0
Comment
Question by:TMilan
  • 3
  • 2
8 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 38728328
Hi TMilan, VLOOKUP with the range-lookup argument as TRUE will find this as per attached sheet.
Hope that helps
...Terry
VLOOKUP.xlsx
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 166 total points
ID: 38729649
VLOOKUP is not going to help here.

Please see the attached sample file:

Q-27980090.xlsx

There are two formulas you could use:
{=INDEX(B1:B501,MAX(IF(A1:A501<C4,ROW(B1:B501),-1)),1)}
{=INDEX(B1:B501,LARGE(IF(A1:A501<C4,ROW(A1:A501),""),1),1)}

The latter is described in my article http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_10590-Finding-the-Nth-Lookup-Value-in-an-Excel-List.html

Note that both are array formulas!  To enter them, do NOT use the curly braces, and use Ctrl+Shift+Enter instead of just Enter to finish them off.
0
 
LVL 16

Assisted Solution

by:terencino
terencino earned 166 total points
ID: 38730131
Hi Patrick, I'm glad you brought that up, it is almost counter-intuitive to expect VLOOKUP to work in this case, with range_lookup as TRUE. But it actually works quite well. I've attached an update of your file with the VLOOKUP added to the comparison of MAX and LARGE.

Appreciate your comments
...Terry
Q-27980090-2.xlsx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38730300
Terry,

Yes, that will work, but only as long as the entries are sorted by date, ascending.  My formulas have no such dependency :)

Patrick
0
 
LVL 16

Expert Comment

by:terencino
ID: 38730318
That's true Patrick, although TMilan advised the data would always be sorted the same way.

B the way I just noticed that when I insert a row above the data range, that the array formulas produced the wrong answer?
...Terry
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 168 total points
ID: 38732369
An option without the need for an array formula:

=INDEX(A9:B14,MATCH(C4,A9:A14,0)-1,2)

Finds the row in the dates column of the date in C4 and then minus 1, assuming always in order, this will be the last row prior to the first match of the date in C4.

The "...,0)" in the MATCH function will look for an exact match of the date in C4. If removed (or changed to 1) it will find the nearest match without going over.

Thanks
Rob H
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

792 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