Solved

Excel Function Problem

Posted on 2012-12-28
8
174 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 31

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

705 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

15 Experts available now in Live!

Get 1:1 Help Now