• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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

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
klitton7
Asked:
klitton7
1 Solution
 
nutschCommented:
If you can sort your worksheet2 on column A, you can use this:

=VLOOKUP(E2,Worksheet2!$A$2:$C$31,3)
0
 
fb1990Commented:
Is this what you are looking for?
Claims-Summary-by-Billing-Specia.xls
0
 
klitton7Author Commented:
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
 
barry houdiniCommented:
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
 
klitton7Author Commented:
Perfect!  Thanks Barry
0

Featured Post

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now