Searching A Table

[PC User]

I've been goggling for a formula to search a table using a planet name to find the day corresponding to that planet. In this table the planet name is directly underneath the day of the week. For example, I have the planet Venus to search the table; the formula should return the day of the week as Friday. The formula should only search two rows (T3:Z4) of this table and return the name of the day of the week. I've tried a number of ways to do this and I cannot seem to get anything to work. The sub-table to search is T3:Z4. Please help.

 

[Yongle]

Place value to find in cell A1 The 2 formulas required are: =INDEX(T2:Z2,MATCH(A1,T3:Z3,0))

<tbody>
</tbody>

=INDEX(T2:Z2,MATCH(A1,T4:Z4,0))

<tbody>
</tbody>

If you want both days in one cell:
=INDEX(T2:Z2,MATCH(A1,T3:Z3,0))&" & " &INDEX(T2:Z2,MATCH(A1,T4:Z4,0))
(Mercury returns Wednesday & Friday)

 

[jtakw]

Hi,

I interpreted the OP's question as such:

Excel 2010
	S	T	U	V	W	X	Y	Z
3		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4		Sun	Moon	Mars	Mercury	Jupiter	Venus	Saturn
5
6	Search	Result
7	Venus	Friday
Sheet32

Cell Formulas
Range	Formula
T7	=INDEX(T3:Z3,MATCH(S7,T4:Z4,0))

 

[PC User]

Your interpretation is correct. I'm getting an error and I cannot figure out why. See the yellow alert box next to the cell with the formula.

Hi,

I interpreted the OP's question as such:

Excel 2010

	S	T	U	V	W	X	Y	Z
3		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4		Sun	Moon	Mars	Mercury	Jupiter	Venus	Saturn
5
6	Search	Result
7	Venus	Friday

<tbody>
</tbody>

Sheet32​

Worksheet Formulas Cell Formula T7 =INDEX(T3:Z3,MATCH(S7,T4:Z4,0)) <tbody> </tbody>

<tbody>
</tbody>

 

[jtakw]

So here you're trying to Match P31, which is Mars, is that right?

The #N/A error would mean that there is No match, how is the value "Mars" entered in P31?
Also, the Table you're looking up as shown in your 1st post, how is that Table generated? Imported, manually entered, etc.?

 

[PC User]

The value "Mars" is a result in of another search formula in P31 which looked up the ruling planet for Aries from another table. I then reference to P31 in the formula contained in P32 to find the "Day of the Week" from the first table that I displayed in post #1 . Is it a problem to reference another cell with a formula? Cell P31 contains a search formula, but displays the results. In P32 I'm trying to reference the result, not the formula in the cell. All tables were created manually and were not generated.

So here you're trying to Match P31, which is Mars, is that right?

The #N/A error would mean that there is No match, how is the value "Mars" entered in P31?
Also, the Table you're looking up as shown in your 1st post, how is that Table generated? Imported, manually entered, etc.?

 

[jtakw]

It's perfectly ok to reference another cell that contains a formula for the result of that cell...
But you didn't tell me Where the Table(s) come from, if it's imported, often times they contain "Hidden Characters", that will cause the values Not matching Up.
Try and see if adding wildcards will do it:

Excel 2010
	P	S	T	U	V	W	X	Y	Z
3			Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4			Sun	Moon	Mars	Mercury	Jupiter	Venus	Saturn
5
31	Mars
32	Tuesday
Sheet32

Cell Formulas
Range	Formula
P32	=INDEX(T3:Z3,MATCH("*"&P31&"*",T4:Z4,0))

 

[PC User]

I'm sorry this is turning out to be such a problem, but the best way to explain the problem is to show you the original spreadsheet. I still can't get it to work. See file link. > https://www.expirebox.com/download/d484cfe8b63047d87af5d1fa3934e47e.html
<click to="" download=""></click><download this=""></download>

 

[jtakw]

To be honest, I was debating on whether to download your file or Not, since it's an XLSM macro enabled workbook, but decided to go ahead but disabled macros.
Was going to ask you to re-upload the file as an XLSX file...

Anyway, your Table is on a different tab in the workbook, and you Didn't reference the tab in the formula, this will work:

Excel 2010
	C	D	E
21	Zodia Sign from Natal Date's Ephemeris:		Aries
22	Positive Planet from Rulership Table:		Mars
23	Negative Planet Paired with Ruling Planet:	#	Mercury
24	Day of Week using Rulership Planet:		Tuesday
TURN Times Method 2

Cell Formulas
Range	Formula
E24	=INDEX(Reference!$T$3:$Z$3,MATCH($E$22,Reference!$T$4:$Z$4,0))

 

[PC User]

Thanks very much. That did it. Next time I'll remember to post the file as an XLSX file.