Searching the Spreadsheet for a Value, then Returning the Contents of the Cell 1 Where the Value is Contained

[truenw]

I have a google sheet im working on (but I also have it built in Excel). The sheet analyses property values and makes predictions.

I have included a link to a sample of the sheet here:

https://docs.google.com/spreadsheets/d/1kjMViyfXUYDmMDP5PutoQbgDBP92oCnqWd_CcLQ5kMY/edit?usp=sharing

I am trying to make so when I enter 4% in B1, it returns the value contained in the cell immediately below the cell containing 4%. In my example, 4% is contained in cell E4. So, when I enter 4% in B1, I would like B2 to display the value contained in E5 ($5.36).

Thanks in advance, excel wizards

 

[jtakw]

Hi,

One way:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Appreciation Set	4.00%
2	10 Year Avg Gain	5.36
3		Appreciation Schedule
4		1%	2%	3%	4%	5%	6%	7%	8%	9%	10%
5	10 Yr Total Apprec.	$8.09	$7.18	$6.27	$5.36	$4.45	$3.54	$2.63	$1.72	$0.81	($0.10)
6	10 Yr Avg Annual Apprec.	$0.71	$0.72	$0.73	$0.74	$0.75	$0.75	$0.76	$0.77	$0.78	$0.79
7	At Purchase | Year 1	$1.00	$2.00	$3.00	$4.00	$5.00	$6.00	$7.00	$8.00	$9.00	$10.00
8	Year 2	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
9	Year 3	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
10	Year 4	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
11	Year 5	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
12	Year 6	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
13	Year 7	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
14	Year 8	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
15	Year 9	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
16	Year 10	$9.09	$9.18	$9.27	$9.36	$9.45	$9.54	$9.63	$9.72	$9.81	$9.90
Sheet174

Cell Formulas
Range	Formula
B2	=INDEX(B5:K5,MATCH(B1,B4:K4,0))

 

[truenw]

Thank you, this works beautifully!

 

[jtakw]

You're welcome.