Dan in Germany
New Member
- Joined
- Jun 17, 2007
- Messages
- 29
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
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