How to reference one cell and also reference another cell to mimic the value

[ZACowboys]

Hello,

My spreadsheet is setup to where cell (B3) is a dropdown list consisted of 3 animals (DOG/CAT/FISH). In Columns F/G/H shows if that specific animal needs that item reflected by an “X”. I'm wanting to come up with a formula to where if you select each name on the dropdown, then Column A will automatically reflect the "X" or a "Blank" if that animal doesn't require it.

For instance, if DOG was selected in B3, then Column A would have the cells with an "X" such as in Column F. Additionally, if you select CAT in the dropdown in B3, the cells in Column A would reflect what has an "X" in Column G. Furthermore, if you select FISH in the dropdown in B3, the cells in Column A would reflect what has an "X" in Column H only.



Thank you in advance for the help!

 

[dreid1011]

See if this does what you want:

Book2
	A	B	C	D	E	F	G	H
1
2
3	Type	Fish
4
5
6
7
8
9
10
11
12		Animal Needs				DOG	CAT	FISH
13	X	Food				X	X	X
14		Water				X	X
15		Flea Prevention					X
16		Grooming				X	X
17		Leash				X
18		Nail Grinder					X
19		Poop Bags				X
20	X	Toys				X		X
21		Training				X
22		Crate				X
Sheet2

Cell Formulas
Range	Formula
A13:A22	A13	=IF(IF($B$3="Dog",F13,IF($B$3="Cat",G13,IF($B$3="Fish",H13,"")))=0,"",IF($B$3="Dog",F13,IF($B$3="Cat",G13,IF($B$3="Fish",H13,""))))

Cells with Data Validation
Cell	Allow	Criteria
B3	List	Dog,Cat,Fish

 

[PeteWright]

Book1.xlsm
	A	B	C	D	E	F	G	H	I
1		Animals
2
3	Type	DOG
4
5
6
7
8
9
10
11
12		needs				DOG	CAT	FISH
13	x	Food				x	x	x
14	x	Water				x	x
15		Flea Prev					x
16	x	Grooming				x	x
17	x	Leash				x
18		Nail Grinder					x
19	x	Poop Bags				x
20	x	Toys				x		x
21	x	Training				x
22	x	Crate				x
23
Sheet6

Cell Formulas
Range	Formula
A13:A22	A13	= IF(XLOOKUP($B$3,$F$12:$H$12,F13:H13,,0)="x","x","")

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$F$12:$H$12

 

[dreid1011]

A shorter option to my original post, and doesn't have to be completely re-written if you change the list values, just update the ranges:

Book2
	A	B	C	D	E	F	G	H
1
2
3	Type	Cat
4
5
6
7
8
9
10
11
12		Animal Needs				DOG	CAT	FISH
13	X	Food				X	X	X
14	X	Water				X	X
15	X	Flea Prevention					X
16	X	Grooming				X	X
17		Leash				X
18	X	Nail Grinder					X
19		Poop Bags				X
20		Toys				X		X
21		Training				X
22		Crate				X
Sheet2

Cell Formulas
Range	Formula
A13:A22	A13	=IF(INDEX(F13:H13,,MATCH($B$3,$F$12:$H$12,0))="X",INDEX(F13:H13,,MATCH($B$3,$F$12:$H$12,0)),"")

Cells with Data Validation
Cell	Allow	Criteria
B3	List	Dog,Cat,Fish

 

[kevin9999]

In case Xlookup() doesn't work in 2016, here's an alternative:

Book1
	A	B	C	D	E	F	G	H
3		FISH
4
5
6
7
8
9
10
11
12		NEEDS				DOG	CAT	FISH
13	x	Food				x	x	x
14		Water				x	x
15		Flea					x
16		Groom				x	x
17		Leash				x
18		Nail					x
19		Poop				x
20	x	Toys				x		x
21		Training				x
22		Crate				x
Sheet1

Cell Formulas
Range	Formula
A13:A22	A13	=IF(HLOOKUP($B$3,$F$12:$H$22,ROW()-11)="x","x","")

Cells with Data Validation
Cell	Allow	Criteria
B3	List	DOG,CAT,FISH

 

[kevin9999]

My suggested formula in post #5 should have been:

=IF(HLOOKUP($B$3,$F$12:$H$22,ROW()-11,FALSE)="x","x","")

 

[Eric W]

A plain INDEX should work:

Book1
	A	B	C	D	E	F	G	H
1
2
3	Type	Cat
4
5
6
7
8
9
10
11
12		Animal Needs				DOG	CAT	FISH
13	X	Food				X	X	X
14	X	Water				X	X
15	X	Flea Prevention					X
16	X	Grooming				X	X
17		Leash				X
18	X	Nail Grinder					X
19		Poop Bags				X
20		Toys				X		X
21		Training				X
22		Crate				X
23
Sheet5

Cell Formulas
Range	Formula
A13:A22	A13	=INDEX(F13:H13,MATCH(B$3,F$12:H$12,0))&""

Note the &"" on the end to avoid displaying a "0" in case of an empty cell.