Solved

Locate a number in a named range and return date in the adjoining cell.

Posted on 2014-03-28
5
259 Views
Last Modified: 2014-03-28
I need to find out when a "filing_batch" was created.  
In the attachment, I need to insert a date in column F based on the filing_batch number in column E.  The table that contains the anser is on worksheet2.

So, for rows 2 through rows 323, the date that should be returned is 2/1/2014, etc.
Claims-Summary-by-Billing-Specia.xls
0
Comment
Question by:klitton7
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39962118
If you can sort your worksheet2 on column A, you can use this:

=VLOOKUP(E2,Worksheet2!$A$2:$C$31,3)
0
 
LVL 1

Expert Comment

by:fb1990
ID: 39962217
Is this what you are looking for?
Claims-Summary-by-Billing-Specia.xls
0
 

Author Comment

by:klitton7
ID: 39962321
I didn't word my question properly it seems...

Every row on Worksheet1 that has data should have a date in column F.  There should be dates all the way through row 6655

So worksheet1,  F22 should return date 2/1/2014 because the filing_batch number 39009 falls inbetween the range of numbers A2-B2 on Worksheet2.

Actually 2/1/2014 would post all the way through row 323.  Then 2/3/2014 will post from row 324 through 1389, etc.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39962670
Try this formula in F2 copied down

=LOOKUP(2,1/(E2>=Worksheet2!A$2:A$31)/(E2<=Worksheet2!B$2:B$31),Worksheet2!C$2:C$31)

see attached

regards, barry
Claims-Summary-by-Billing-Specia.xls
0
 

Author Closing Comment

by:klitton7
ID: 39962707
Perfect!  Thanks Barry
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 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

12 Experts available now in Live!

Get 1:1 Help Now