Find Closest Value in a Table of Values

[CT2243]

Hello. I'm trying to find the closest match to a given value using a specific category. For example, I need to be able to look up the values in Column B in the table in F1:K14 and find the closest value to the corresponding rates in Column C. So the formula for D6 would need to (1) look up the category IT 02 and then (2) find the closest value in the table to match $20.32. This is just a subset of values I need to look up so I'll need to be able to copy the formulas down to cover all the Categories I might need. I figured it would be a combination of Index and Match for the rates, but I can't figure how to get the Category variable in as well. Any assistance would be greatly appreciated.

screen.jpg

[MrShorty]

A sample spreadsheet would be better than a picture. I cannot test this on a picture, but this is how I would expect to go about this (not necessarily in a single cell formula).

1) A MATCH() function based on category to find the row number. MATCH(B6,$F$6:$F$14,1) Your sample shows the categories sorted in ascending order, so I used the faster binary search (3rd argument is 1). Change to 0 if you need this to be an exact match.
2) An INDEX() function to get the row found in step 1 (note that, by using 0 as the column # argument, INDEX() will return the entire row. INDEX() help file: https://support.office.com/en-us/art...2-b56b061328bd ). INDEX($G$6:$K$14,result from 1,0)
3) A second MATCH() function using the "approximate" match option (rates are sorted ascending left to right, so the third argument will be 1) to find the "closest" rate and return its column #. MATCH(C6,result from 2,1)
4) A final INDEX() function to get the "closest" rate INDEX($G$6:$K$14,result from 1,result from 3)

Note that "closest" in this approach means "largest value in a given row that is less than or equal to the rate in column C". For example, IT 07 and $61.00 is going to find the 57.46 value, not the 62.94 value. Also note that a rate smaller than the point 1 rate will return N/A. You will need to think a bit about exactly what "closest" means to you and adapt this accordingly. If I wanted something like IT 07 and 61 to return the 62.94, I would add a range of lookup values to mark the desired threshold points where it will start to return the larger value instead of the smaller value. This range might look like:

Please Login or Register  to view this content.

this helper range goes into the lookup formulas in steps 2 and 3.

[CT2243]

I think I attached the file properly...
To address a few of your points...
Rates will never be smaller than Point 1.
Closest does not have to be less than or equal to the rate in Column C. It should just be an absolute closest match.

[tim201110]

=index($g$4:$k$12,$e4,match(aggregate(15,6,abs($c4-$g$4:$k$12)/($f$4:$f$12=$b4),1),abs($c4-index($g$4:$k$12,$e4,)),))