CODEWITHSUNDEEP
excel
Zahid Rouf
Zahid Rouf

Reputation: 1751

cannot get nearest column value using Match in excel

I want to get the nearest column value . For Example: there are two columns, 50,40 . When the input will be 45, it will select column number under 40, but it selects column under 50. I have used -1 for descending order of match function .

Here is the table: enter image description here

Anyone know how to get the nearest column in Excel ?

Upvotes: 0

Views: 57

Answers (3)

barry houdini
barry houdini

Reputation: 46371

To get nearest match use this array formula

=MATCH(MIN(ABS(C3-A1:E1)),ABS(C3-A1:E1),0)

confirmed with CTRL+SHIFT+ENTER

That works whatever the order of A1:E1

If you want to match with greatest value <= to C3 try this version

=MATCH(MAX(IF(A1:E1<C3,A1:E1)),A1:E1,0)

Upvotes: 1

Ken
Ken

Reputation: 1166

From the documentation, when using -1

MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

EDIT

=COUNTIF(A1:E1,">"&C3)+1

:)

Upvotes: 0

durbnpoisn
durbnpoisn

Reputation: 4669

The function you used returns the following: 1=less than, 0=equals, and -1=greater than. So, returning 1 is correct as only one value is greater than 49.

Upvotes: 0

Related Questions