Solved

Excel Function Problem

Posted on 2012-12-28
8
206 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 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

734 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