INDEX & MATCH Formula With A Specific Amount Of Sensitivity

Table 1

+ On the column F there are frequencies as number. (12 digits after decimal point.)

+ On the column C there are names of those frequencies as text.

Table 2

+ On the column Z I get those frequencies by calculating in a way. However sometimes last digits don't matched with the numbers in the column F.

+ So INDEX & MATCH formula doesn't work on the column X.

1) Is it possible to add a sensitivity feature to that INDEX & MATCH formula so that the problematic cells get matched?

2) Let's say I convert the frequencies cells into text format by TEXT() formula. So, can we create a TEXT format version of the formula I mentioned at 1)?

The references on the file I uploaded is different than I mentioned previously.


Check out the formulas on the range A19:A22.


When I set MATCH Type as "0" It finds nothing


When I set that as "1" It finds a value on the previous row on the match column.

On the cell "A19" when I set "1" It returns C6 (I want it returns C7)

Same thing happens on "A20" It returns "C10" though I want C11.


Maybe I need a formula which uses MATCH type "0" If it gets an error "#N/A" It uses MATCH type "1" with OFFSET (one row higher)

Does that do the trick?

There is another issue on my case which is "Floating Point Precision" so that even if the numbers are the same It still don't match with some numbers.

I tried ROUND with all the values from 2 to 15. Some numbers worked for some cells and don't work for some cells.

I think my solution I mentioned may be the best for my situation. Is that formula possible?

"Maybe I need a formula which uses MATCH type "0" If it gets an error "#N/A" It uses MATCH type "1" with OFFSET (one row higher)

Does that do the trick?"

I tried it on some cells It looks like It works.

1) Why ROUND(VALUE, (Numbers from 2 to 15)) does not work and your formula work?

2) What does second argument do on ROUNDUP function? On the explanation page it says "away from zero" which I don't understand.

What does that mean that you do not enter a value for 3rd argument of "MATCH"?

Thank you very much for the detailed answer Bo_Ry and Sambo Kid. It helped a lot.