Formula to pull data from a chart based on a certain lookup value...

Hello. I have a set of data with about 10,000 lines of data. I am working with insurance rates and the year that the rate was issued. Each line has a year value as well as a dollar value in a different cell. On a separate tab I have all the rates for each year and text values adjacent to the rates. For example:

2010
-660 Outbound
-320 Outbound
50 Inbound
60 Inbound
135 Outbound
165 Outbound

2011
-675 Inbound
-660 Outbound
-320 Inbound
50 Inbound
61.5 Outbound

On the other tab that has my 10,000 lines of data, I want to create a formula for each line that will take the year that the rate was issued, and than lookup the insurance rate in the table that corresponds to that year. But I want the formula to tell me if the rate is either Inbound or Outbound.

So in summary, the 10,000 lines of data will look something like this:

Column A (Insurance Rate): -660
Column B (Issue Year): 2011
Column C (Inbound/Outbound): This is where I would want the formula

*Keep in mind that the rates are broken out by Issue Year on a separate tab like I showed above.

Any thoughts?

-Garrett

Assuming on sheet2 you have the number values in col A from lowest and inbound/outbound in B
With year in B2
=VLOOKUP(A2,INDEX(sheet2!$A$2:$A$100, Match(B2, Sheet2!$A$2:$A$100, 0)+1):INDEX(Sheet2!$B$2:$B$100, MATCH(B2+1, Sheet2!$A$2:$A$100,0)-2),2)
Does that work for you?
An example workbook might help (go Advanced>Manage Attachments)

Just make a copy with enough data so we can see what you are trying to do. Remove the extra data and sheets that don't relate to the problem. Try zipping the file also.

Were you able to understand the formula that I presented? I assume it didn't work if you want to upload the sheet.

I inputted my formula into X and Y (similar to what I suggested in Post #2). Let me know if it is returning the correct values and if not, which ones and what did you expect for those.

Edit: Note, I added the year 2015 to the bottom of your Lookup Table. The formula defines the boundaries of what it is looking up by the year and next year so would cause an error when there is no next year.

Typo on my part. I had put a D2 instead of a D6

Correct formula (dragged down) in Y3

=VLOOKUP(S3,INDEX('Lookup Table'!$A$6:$A$100, MATCH(X3, 'Lookup Table'!$A$6:$A$100, 0)+1):INDEX('Lookup Table'!$D$6:$D$100, MATCH(X3+1, 'Lookup Table'!$A$6:$A$100,0)-1),2)

Just change that final 2 (returning from column 2) to a 3 (returning from column 3)
Glad to help

You will always need to put the next year after your last row (that's how it knows where to end). Other than that (and the proper range) yes, it will work.