Solved

Excel Function Problem

Posted on 2012-12-28
8
211 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 …
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

627 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