Find a cell in a table, and return the value above

[DannyC96]

So I'm trying to return a cell value from a table, relating to a text based cell as the searching tool. It's actually part of a larger formula, but the rest is straight forward.

I am trying to return a result where if the day of the week changes in A2, it changes what cell I use in a formula in C2. So in this example, I am adding C6 (Tuesdays total) to B2 (Wednesdays so far). However, what I want to do is if I change the value of A2 to Thursday, then it automatically changes C6 to C7 in the formula in C2.

So I've used VLookups, but not sure how to vlookup for, say, Wednesday, but return the Tuesday total in C6 for the formula to work.

	A	B	C
1	Today	People Today So Far	Expected Total People
2	Wednesday	5	FORMULA
3
4	Days	Regular People	Total People
5	Monday	2	2
6	Tuesday	6	8
7	Wednesday	7	15
8	Thursday	4	19
9	Friday	3	21

 

[Alex Blakenburg]

I expect there will be more to it than this but let's start with this:

Book2
	A	B	C	D
1	1	Today	People Today So Far	Expected Total People
2	2	Wednesday	5	13
3	3
4	4	Days	Regular People	Total People
5	5	Monday	2	2
6	6	Tuesday	6	8
7	7	Wednesday	7	15
8	8	Thursday	4	19
9	9	Friday	3	21
Sheet1

Cell Formulas
Range	Formula
D2	D2	=C2+IF(B2="Monday",0,INDEX($D$5:$D$9,MATCH($B$2,$B$5:$B$9,0)-1,0))

 

[DannyC96]

I expect there will be more to it than this but let's start with this:

Book2
	A	B	C	D
1	1	Today	People Today So Far	Expected Total People
2	2	Wednesday	5	13
3	3
4	4	Days	Regular People	Total People
5	5	Monday	2	2
6	6	Tuesday	6	8
7	7	Wednesday	7	15
8	8	Thursday	4	19
9	9	Friday	3	21
Sheet1

Cell Formulas
Range	Formula
D2	D2	=C2+IF(B2="Monday",0,INDEX($D$5:$D$9,MATCH($B$2,$B$5:$B$9,0)-1,0))

It works! Ideal

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.