Searching through a table with multiple criteria

[iangill]

I have an excel sheet that I'm trying to search through multiple criteria to check that the term of something does not exceed the allowable length.

There are multiple criteria that change, including a "Tier", "Year", "KM's" and "Term". I need to reference my table to match up the tier/year/km's/term to ensure the TERM does not exceed the allowable one.

I got it working using a whole bunch of nested IF AND statements however I have exceed the 64 allowable and need another option.

Example of the statement i had that was working:
A87 = TIER
A92 = YEAR
C44 = MAX TERM
A95 = KM's

=IF(AND(A87=2,A92=2008,C44>12,A95<=180000),12,
IF(AND(A87=2,A92=2009,C44>24,A95<=180000),24,
IF(AND(A87=2,A92=2010,C44>36,A95<=165000),36,
IF(AND(A87=2,A92=2010,C44>30,A95<=180000),30,
IF(AND(A87=2,A92=2011,C44>48,A95<=180000),48,
IF(AND(A87=2,A92=2012,C44>60,A95<=130000),60,
IF(AND(A87=2,A92=2012,C44>54,A95<=165000),54,
IF(AND(A87=2,A92=2012,C44>48,A95<=180000),48,

I'm thinking I need to VLOOKUP but not sure how to go about it with multiple criteria needing to match in multiple rows.

I've attached a link to a picture of what i'm try to do (because i cannot upload an excel for some reason):

imgur.com/gallery/jiQO7SK

[FDibbins]

Hi, welcome to the forum

You might be able to do this with INDEX/MATCH, but to help more, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[iangill]

Thank you I do believe the INDEX/MATCH function will work.

The match function seems to work if everything matches EXACTLY, however the km's is never going to be an exact number. How do i get it to check if its maybe within a range of numbers?

For example:
Submitted KMs: 167500

Tier Year Max KM's Max Term
2 2010 165000 36
2 2010 180000 30

How do I get it to know that the max term for those km's is 30 and not 36 when the km's do not match the table?

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[iangill]

Added sample.

I need a way for the function to work when the km's are not an exact number to the number in the table (a range?). Cannot figure this out... Thanks!

[AliGW]

Nope. Try again.

[FDibbins]

I dont see any difference between tier 2 and 3? Does your real data differ from the sample?

Also, are you looking to find the term based on the lowest closest KM's

[iangill]

That is just a small sample, there are more tiers/years than that data but I figured it would show what i'm trying to accomplish.

For example:
Submitting information of:
Tier 2
Year 2010
KM's: 170000
Term 30

Tier Year Max KMs Max Term
2 2010 165000 36
2 2010 180000 30

The submitting information qualifies for the term of 30 months (as it is greater than 165000km's). So I guess I'm looking to confirm the term based on the highest closes km's based on Tier/Year.

The formula I have in the excel sheet seems to work only if the KM's submitted are exactly the same as on my table - is there any way to make it confirm a range instead of exact number?

[JeteMc]

If I understand correctly then I believe the following formula will yield correct results:

Please Login or Register  to view this content.

Let us know if you have any questions.