Thread: Lookup closest value azimuth (0-360°) value in specific range and return over 360°

1. Lookup closest value azimuth (0-360°) value in specific range and return over 360°

Dear

I want to look up the closest value to a given set of azimuths.
The given azimuths (B2:B5) change per project and in some cases a 4'th sector will be given. The formula should thus work with both 3 and 4 given azimuths. (azimuths from different sectors will not be identical).

The goal is to establish for each 'checkpoint's' azimuth value (column F) the closest sector azimuth. In the example, this 30°, 160° or 250°.
Amount of checkpoints are unknown.

Currently i'm using the following formula, this works great, except that the formula doesn't return after 360°.
For example, checkpoint 6 is located on azimuth 345,4° --> this is closest to sector 1's 30°. But my formula returns S2 (250°) since the formula doesnt 'return' after 360°.

This is my current formula: =ALS(OF(\$B\$2="";\$B\$3="";\$B\$4="");"Missing AGL data!";ALS(F6="";"";ALS.FOUT(ALS(F6="";"";VERT.ZOEKEN(INDEX(\$B\$2:\$B\$5;VERGE LIJKEN(MIN(ABS(\$B\$2:\$B\$5-F6));ABS(\$B\$2:\$B\$5-F6);0));\$B\$2:\$B\$5;2;ONWAAR));ALS.FOUT(ALS(F6="";"";VERT.ZOEKEN(INDEX(\$B\$2:\$ B\$4;VERGELIJKEN(MIN(ABS(\$B\$2:\$B\$4-F6));ABS(\$B\$2:\$B\$4-F6);0));\$B\$2:\$B\$4;1;ONWAAR));"Missing AGL data!"))))
Formula is also present in the attached example sheet, incorrect fields are marked in Red.

Thanks for your assistance.

Kind regards
Jeroen  Reply With Quote

Tags for this Thread

compare, index, lookup, return, values  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•