How to find nearest value in excel?

[AhsanQatar]

Dear All,
I need to find nearest value(point). how can find in Excel or another software?
For the same value i can use "vlookup" but for nearest i don't know.
please guide me.
Please check the attached file for your reference.

[Glenn Kennedy]

What do you mean by nearest?

The nearest value above, below, or either?
Applied to Eastings, or Northings, or both? If both, how?

[Glenn Kennedy]

Based on nearest EASTINGS:

E3
=INDEX(K:K,MATCH($F3,$L:$L,0))

F3:
=LOOKUP(0,0/FREQUENCY(0,ABS($L$3:$L$21-B3)),$L$3:$L$21)

G3:
=INDEX(M:M,MATCH($F3,$L:$L,0))

[AhsanQatar]

Dear Mr.Glenn Kennedy,
Thank you so much.
It's working, it's really good solution.
You saved my time.
Once again Thank You

[AhsanQatar]

Dear Mr.Glenn Kennedy,

You asked yesterday like this,
"What do you mean by nearest?

The nearest value above, below, or either?
Applied to Eastings, or Northings, or both? If both, how?"

Today only i found the issue

With your previous method it's not possible, i have attached same file with some changes for better understand.

Is there any option to find out with the nearest with "distance"?

Please guide me.

[AliGW]

If you need further assistance, I suggest you remove the SOLVED tag from the thread.

[Glenn Kennedy]

Try this out.

If it's incorrect, please use cell shading on this sheet to show correct results.

[Glenn Kennedy]

A bit more research has produced this. There are now 3 choices for you (all yielding the same answer). I have changed the data layout a little (re-ordering columns, nothing more) as I'm going to keep this solution on file...

Option 1:
=LOOKUP(1,1/FREQUENCY(0,MMULT(($F$3:$G$21-$B3:$C3)^2,{1;1})),E$3:E$21)

copied across and down.

Option 2:
=LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(F$3:F$21-B3))/2)^2+SIN((RADIANS(G$3:G$21-C3))/2)^2*COS(RADIANS(F$3:F$21))*COS(RADIANS(B3))),F$3:F$21)

array-entered and copied down (the most accurate formula, apparrently!!). Then a simple INDEX-MATCH to return the other two parameters.

Option 3:
INDEX(E$3:E$21,MATCH(MIN(SQRT(($F$3:$F$21-$B3)^2+($G$3:$G$21-$C3)^2)),SQRT( ($F$3:$F$21-$B3)^2+($G$3:$G$21-$C3)^2),0),1)

array-entered copied across and down. It's based on the SQRT of the sums of squares of the differences, as per your manual calculations.

See sheet for data layouts.


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[AhsanQatar]

Dear Mr.Glenn Kennedy,

Thank you so much,
Option 2 is not giving correct value.
But other two options are very good solution.

Thank you for spending your valuable time for me.
Really you are very good. God bless you.

Once again thank you

[Glenn Kennedy]

For my own interest, can you show me an example where it DOES NOT work? My guess is negative eastings or northings???


You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.