asked on

Looking up minimum value position in a range subset

A 2
A 6
A 12
B 1
B 2
B 3

How could I write up an array function in Excel to return the position of the row in a subset A, where the second column is the closest to number 5?

I'm thinking of something along the lines of {=MATCH(1, IF(AND(A:A="A",B:B=MIN(IF(A:A="A",ABS(B:B-5),999))),1,0),0)}

Hope the above makes sense.

Thanks

Saurabh Singh Teotia

You Okay with a user defined function for the same??

Saurabh...

saulius88

ASKER

I would much prefer an array function - a UDF would make sharing an issue, it also is less "tidy".

saulius88

ASKER

Sorry, I should have added, the expected result is "2" - i.e. second row.

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

Maybe this.....(Array Formula)

=MIN(IF(A2:A7="A",ABS(B2:B7-5)*(ROW(A2:A7)-ROW(A2)+1)))

Open in new window

That will give you the row number of A subset where corresponding value in col. B is closest to 5.

I think this is what you asked in your question.

Does this help?

Professor J

i did not see the criteria of closest to 5.

the formula from sktneer perfectly works for you

saulius88

ASKER

Really helpful, thank you.

saulius88

ASKER

Sorry, sktneer, I was testing out the solution that ProfessorJimJam provided before I saw your posts. Both solutions would work, I suppose. Thanks both.

Subodh Tiwari (Neeraj)

No problem.

But you wrote this.....

How could I write up an array function in Excel to return the position of the row in a subset A, where the second column is the closest to number 5?

What was that?

If that was your requirement, how could you get the correct output with the formula suggested by ProfessorJimJam?

saulius88

ASKER

I tweaked the formula slightly, before seeing your solution:
=MATCH(MIN(IF(A1:A6="A",ABS(B1:B6-5))),ABS(B1:B6-5),0)

Since both your formulas work and were submitted before I hit "accept" I will request that the points are split.

Thanks again.