Dual Reverse Lookup [Row & Column Headers]

Dear Friends:

I have a problem which is self explanatory in the attached Diagram. I am aware that this needs a Dual Bidirectional Reverse Look up which needs to be 'AND'ed to get a [1, 0]. I have been through examples of 'A single Reverse Lookup' (i.e. Column or Row Header). What I need is a formula that could be applied right across the bottom array here that will pick up the data from the top array to produce a 1 or a 0 in each cell.

Thanking you in advance for any help.

Problem.JPG

Thank You. Will do that now...

Here is the Worksheet.. nothing much more than my image... what I need to do is to have a '1' in B13 & D11

or this (may be identical to JTs...)

=IF(AND(ISNUMBER(MATCH(B$10,B2,0)),ISTEXT(INDEX($B$2:$H$8,MATCH(B$10,$A$2:$A$8,0),MATCH($A11,$B$1:$H$1,0)))),1,0)

Originally Posted by JohnTopley

Try

in B11

=IF(AND(INDEX($B$2:$H$8,MATCH($A2,$A$2:$A$8,0),MATCH(B$1,$B$1:$H$1,0))=B$1,INDEX($B$2:$H$8,MATCH(B$1,$A$2:$A$8,0),MATCH($A2,$B$1:$H$1,0))=$A2),1,"")

Copy across and down

Dear John:

Brilliant... Absolutely Brilliant. My idea was to get a '1' on those matching cells in the bottom array so that I could Highlight them corresponding cells in the top array. Have a look at the Image.

You might be curious as to what this is all about. Astrology. Planets Sun, Moon & Saturn can see others in Positions 3 & 10 ahead. Mars & Mercury 4 & 8; Jupiter & Venus 5 & 9. All planets can see the 7th house ahead (Opp. 180°). It follows that some planets can see others but not have a reciprocal aspect unless they are in the 7th or either Jupiter or Venus in 1, 5, 9 Positions.

Thank you very much once agin. I will mark this as [Solved] and Vote 1 for you.

Glenn RIC: Will try your solution as well and revert. Thank you.

Solved.JPG

Originally Posted by Glenn Kennedy

or this (may be identical to JTs...)

=IF(AND(ISNUMBER(MATCH(B$10,B2,0)),ISTEXT(INDEX($B$2:$H$8,MATCH(B$10,$A$2:$A$8,0),MATCH($A11,$B$1:$H$1,0)))),1,0)

Dear Glenn:

Thanks for the reply. There seems to be a problem with the formula. It is simply replicating the text on the upper array with 1's in the bottom array without cross-checking if the same set of headers are transposed under their respective columns and rows.

For examples: Mars here can 'see' Mercury but Mercury in return can't 'see' Mars. Sun can 'see' Saturn but Saturn in return can't 'see' Sun.

See the comparison below obtained with the results of JT's formula.

Both.JPG

Glad you got sorted.

JT: proves (in this case anyway) that smarter <> correct..... !!!

Thank you very much...