Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Function Problem

Posted on 2012-12-28
8
Medium Priority
?
223 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
[X]
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
  • 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 664 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 664 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 93

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 672 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

664 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