Tomkat is trying to cap mileage paid based on production. The mileage cap varies based on production and site. Read this newsgroup post for details if you like. This newsgroup post is where it all started.
This calls for a VLOOKUP/MATCH combo if I ever saw it.
The formula in E15 (and filled down column E) is
MATCH: The MATCH function returns the column that matches the site entered. It’s used as the col_index_num argument of VLOOKUP, that is, which column VLOOKUP retrieves. You have to add two to the result because the range of “Sites” starts two cell right of the vlookup range.
VLOOKUP: The left-most column of the mileage cap table is filled with the lowest number of the appropriate range of numbers. Column B shows the range of numbers, but it’s just there for informational purposes. Because there is no fourth argument in the VLOOKUP, it will find the largest number that is not greater than the number it’s looking for. If, as in row 15, we’re looking for 342, it will find 300 because that’s the largest number in the list that’s not greater than 342.
MIN: The whole thing is wrapped in a MIN because it’s a cap. If the person claims less than the cap, then that value should be used, not the cap.
I hope that helps Tomkat.