Determining the range of an angle on a 360 modulus using XLOOKUP or...

Hello All,

This is my first time posting, although I have found numerous helpful examples . (Thank You!)

I am attempting to use XLOOKUP to determine if a cell value falls within the range of one of twelve House positions, (Column A) and returning the proper house position in row 23 (Columns C > MX)

I found a Formula that seems to work, [ =XLOOKUP(0,ABS(C$2:C$13-C22), $A$2:$A$13, ,1) ] but some of the values returned either round up or round down returning the inappropriate House value.

ex: C23 should be 6th instead of 7th, E23 should be 5th instead of 6th, G23 should be 4th instead of 5th

I think I may be looking for a Nested If Statement, but cannot wrap my head around a proper way of determining if a value lies within a house that has a beginning value of 359 and an ending value of 29.

ex:Cell C9

Lookup example.jpg


I appreciate any guidance anyone can provide, Thank you.

Hi!

One option could be:

PHP Code: 

=XLOOKUP(C22,C2:C13,$A2:$A13,"",-1) 


And drag it to the right. Blessings!

Hi again!

Is this what you looking for? (any of these):

PHP Code: 

=XLOOKUP(C22,C2:C13,$A2:$A13,XLOOKUP(C22,C2:C13,$A2:$A13,"",1),-1)
=LET(h,XLOOKUP(C22,C2:C13,$A2:$A13,"",{-1;1}),@FILTER(h,h>"")) 


Blessings!

John,

C'est Bon!
Both are Great...
You Have opened up a new world to me. (especially with the second Formula)

Thank you Friend.

Josephteh, thank you for the additional formula.

I did manage to create four rows with all of the previous formula suggestions... and I noticed that when a Cell value is in between 340>10 degrees that the House reflected is the next greater number.

Ex: Column J... Cell J23, J26, J35 all reflect "10th" which is the next value in the progression, but the Value of 6.909 is actually between 354.82652 and 24.82651 which would be "9th" is there any way to show a value of "9th"?

I thought that the code(s) would work, but just noticed this. I am stumped on how to correct.

I think what I am trying to accomplish is to have a set of ranges that change in each column, and to reflect where a value lies on a 360 degree modulus

The Ranges lie on a 360 Circle, but change in each column.


ex: "Column J"

1st= 114.82652 >>> 144.82651

2nd=144.8652 >>> 174.82651

3rd=174.82652 >>> 204.82651

4th=204.82652 >>> 234.82651

5th=234.82652 >>> 264.82651

6th=264.82651 >>> 294.82651

7th=294.82651 >>> 324.82651

8th=324.82652 >>> 354.82651

9th=354.82652 >>> 24.82651 This Is where I am having difficulty understanding

10th= 24.82652 >>> 54.82651

11th= 54.82652 >>> 84.82651

12th= 84.82652 >>> 114.82651

Thank You.

Hi to both!

What about:

PHP Code: 

=VLOOKUP(IF(C19<MIN(C2:C13),MAX(C2:C13),C19),SORT(HSTACK(C2:C13,$A2:$A13)),2)
=XLOOKUP(IF(C19<MIN(C2:C13),MAX(C2:C13),C19),C2:C13,$A2:$A13,"",-1) 


Blessings!

Hi John and Josephteh,

Both of your solutions work and are very elegant.

Josephteh, I appreciate the pointing out that HSTACK does not work for Excel 2020/2021 ... I guess i need to purchase an Office 365 Subscription, as It seems to have ALL the really interesting functions that make life easy

I owe you both a debt of gratitude... Thank You!


An interesting bit of info, Excel is not not my strong point, (thank you both again for the help) but I do study/practice Astrology.

In one particular discipline of Astrology called "AstroCartoGraphy", a person's birth chart can be laid out on a map of the world. One can then use this map to find places that are more favorable than others for particular outcomes.

When I posted in this forum (after Lurking for quite some time) You two were the ones that provided me with assistance.

If you look at the Image I've attached I have circled the areas on the map where your locations are (from your Screen Name info ) and these positions corresponded with My Natal Sun and Mercury Position.

John's Location is at the top of the Sky when I was born, and Josephteh's location is at the bottom of the sky (when i Was Born) ... these positions are 180 degrees apart from each other (on a 360 degree circle) .


Neither one of you have to make a comment to this, but I would wager that Josephteh has a significant Aries (Tropical Zodiac position) influence and John Has a Significant Libra (Tropical Zodiac position) influence.

I could be wrong but I found it interesting, Either Way you are both very kind and generous individuals.


Thank you Again!!

Thanks quixotic77! That is so curious. And thanks to Josephteh for told me about the version. I hope you can (both of you) get M365 soon and try the nice functions that are there. I'm glad to help. Blessings!