# Match a value against a date and enter the matched value

• [xpost][/xpost]

I am struggling with trying to get a formula that will help to do the following we have the date broken down into individual cells , D D = AN1 and AO1 from AN1 to AU1 In the table , an identifier is indicated from F9 to P9 /Q9 toAA9 and finally from AB9 to Al9 We need to look at the first two digits of the DD ie AN1 and AO1, if the day is from the 1st of the month to the 9th of the month, we ignore the D D I am struggling with trying to get a formula that will help to do the following we have the date broken down into individual cells , D D = AN1 and AO1 from AN1 to AU1 In the table , an identifier is indicated from F9 to P9 /Q9 toAA9 and finally from AB9 to Al9 We need to look at the first two digits of the DD ie AN1 and AO1, if the day is from the 1st of the month to the 9th of the month, we ignore the D D From the 10th day of the month to the end of the month, we will now sum the two digitd ie D+D and if its greater than 9 , we mod it to a single digits We will get an answer, this answer we need to match , wth the identifier and then place in cell below the highlighted yellow cell for example if N9 = 4 and the result from the calculation is 4, we paste the 4 into N10 Likewise, we do the same for Y9 and Y10 and AJ9 and AJ 10From the 10th day of the month to the end of the month, we will now sum the two digitd ie D+D and if its greater than 9 , we mod it to a single digits We will get an answer, this answer we need to match , wth the identifier and then place in cell below the highlighted yellow cell for example if N9 = 4 and the result from the calculation is 4, we paste the 4 into N10 Likewise, we do the same for Y9 and Y10 and AJ9 and AJ 10

## Files

• Hello and Welcome to the Forum

Before getting into your underlying logic for the function ...

Could you clarify : say day is 28 th ... so 2+8 = 10 ... and 10 / 10 is reduced to 1 ...

Is that right ???

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Looking at your numerology sample file ... are you after following formula

Code
``=MOD((AN1+AO1)-1,9)+1``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Many thanx for the response

Yes that is correct

yes the formula is correct

but we need to first look at N9 its 4 so if the date is from the 10th onwards, the above formula is perfect, the results must be checked against N9 and if it matches it paste in N10

• Glad to hear the formula is working as expected ...

Now let's move to your logic ...

The Number 4 appears 11 times in range F9:P9 .... so ... why should the result appear in Column N ... and not else where ???

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• hi

This is a loshu grid

yes each cell represented some characteristic that are matched against the date of birth

The N9 is what e call pyschic number in the loshu grid

• Thanks for giving me the Name of the Magic Square ...

My question is : WHY ... the number must appear in N10 ... and NOT in another Column ... from F to P ...???

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi

Many thanx for the attachment

The first column is not day but its the wood element the second is the fire element and third the earth element

in our example the DD is 4th so in theory it will not fill in N10 but if its the 13th it will fill in N10 because 1+3=4

• Sorry but your explanation is not clear at all ...

Quote

in our example the DD is 4th so in theory it will not fill in N10 but if its the 13th it will fill in N10 because 1+3=4

the DD is 4th so in theory it will not fill in N10 .... SO... in practice ... WHERE AND WHY ... ???

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• the psychic number is derived from the D D of the date of birth if the D D is between the 1st and 9th of the month, we don't do anything

but if the date is between the 10th to 31st we sum the two D +D and get a total this total is reduced to a single digit and this get s pasted onto the grid in the specific spot

The reason this is done with the psychic because in calculations for reading of the chart, the psychic number filling the specific spot will have a value which influence's the final reading

• hi

am attaching a loshu grid that has been completed

may help

The formula I used before always pasted the value of Psychic number , so when the date is before 10th, the valuse gets influenced so we need to separate that , the psychic number is only pasted in after the 1oth of the month

## Images

• Attached is your test file

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Sadly ... Pictures DO not help at all

Much more effective to attach the Excel file ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi

Thanx a span

I need the number to be pasted on the grid that I attached

I need to understand ... when there are ... in your sheet ... 11 times the same number 4 ...

WHY .... it is the 9 th one which turns yellow .... and not another one among the 11 ... ????????

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi

I am attaching the actual spreadsheet

The cells are filled in based on the first two cells which is D D the next two which is M M and the next four which is the Y Y Y Y

the ninth one is the P(psychic) the next the LP(LIfe Path and finally the K(ua)

when reading a prediction, we add values to each of the cells and derive at a reading figure which we analyze

the D D filling the first cells will not influence the 9th Psychic on if the date is from the 10th onwards so then a value is assigned

this is when we look ad repeating numbers and missing numbers

Rajan

## Files

• Well in cell N10 you are using following formula :

Code
``=IF(COUNTIFS(\$BF\$1:\$BF\$1,\$N9)>0,\$N9,"")``

In its construction ... it is not logical ...

Can you write in plain English what you want to accomplish with this formula ?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• what I want to accomplish is that if the date is between 1 to 9th of the month in N10 nothing should appear likewise in all related cells where the pyschic number should be pasted

if the date is between 10th to 31st , the psychic number should be pasted in

• Thanks for the explanation ...

In cell N10, you could test following formula

Code
``=IF(DAY(AW1)<10,"",BF1)``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi

many thanx is working brilliantly

only small snag

it need to check the N9 first and then paste

regards

rajan

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!