problem with the match_type in =match

[Dan in Germany]

Hi

I can use the match function to find the column that I need, however, when setting the match type I would like to have the function find the closest match regardless if the number is higher or lower that what I'm searching for.

ex. I'm searching for number 49 in a matrix

35 40 45 50

when match type is set to 1, the result would be column 3 and not column 4, even though 49 is actually closer to 50 and would be a better match. I know that I would get a better match in this case if I reverse the matrix and put it in descending order and set the match_type to -1. However, I MUST leave the the matrix in ascending order because the column number is associated with a particular growth class, and the data has different match_type requirements (some data needs to be matched with -1 and some with +1, some data doesn't matter)

Is there a way around this problem or is there another formula to use? I just want a formula which tells me in which column a certain number lies, and if theres no exact match, it should tell me in which column the closes match is to be found.
thanks alot
Dan

 

[Dan in Germany]

First of all, thanks for the input, but unfortunately I'm still having difficulties.

I'm pressing CNTRL+SHIFT+ENTER after the formula but I'm still getting the error message. I've double checked the formula many times to make sure that I typed it correctly but it still wont work

I was able to correctly use the example in the link Beate sent me, however. I don't know why that would work on my computer but not the formula you guys are sending me. Beate, even when I use the German version of the formula I still get the error message.

I'll keep trying with the formula's, thanks again for the help.
Dan

 

[Dan in Germany]

Hi again,

just an update: I've gotten the formula to work on my computer ONLY when I copy and paste the html example that Beate posted into my excel sheet. I use the german version of the formula in this sheet and the formula works perfectly! I've typed in the formula by hand and copy and pasted it, it works both ways

HOWEVER, if I try to use the formula anywhere else I will always get the VALUE error. I've double checked many many times that the formula is typed correctly and that the data ranges and referenced cells are correct, and I will always get the error. Only when I copy and paste the html example from Beate will the formula work, and only with the data in the example.

Is there then some kind of formatting issue with my excel program?
why would this happen?