Select last number to the far right of a row.

[nparsons75]

Is there a formula that will allow me to select the far most value to the right of a row? Also, the value should only be selected if it appears under the day of the month - 1. (yesterday)

For example, in the top table below the value to the far right = 10 This will be returned in to my result cell as the value does sit under yesterdays day of the month.

In the second example the cell = blank on yesterdays day of month and therefore the result in my result cell will = 0.

I hope this makes sense.....

Book10
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
6	Result	10
7
8	Day of month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
9	Value	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
Sheet1

Book10
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
14	Result	0
15
16	Day of month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
17	Value	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12
Sheet1

 

[nparsons75]

Ok great that seems to have got it....I have the result 10 now showing in C2. How would I replicate for C3 and C4?

 

[Fluff]

In C2 dragged down

=HLOOKUP(TODAY()-1,$C$7:$AG$10,MATCH(B2,$B$7:$B$10,0),0)

 

[nparsons75]

Sorry im not sure how you mean. Do I replace the existing formula with this =HLOOKUP(TODAY()-1,$C$7:$AG$10,MATCH(B2,$B$7:$B$10,0),0) then drag down?

 

[Fluff]

That's right.

 

[nparsons75]

Ok I must be doing something wrong, it worked but returns the same result as the top cell product 1?

 

[Fluff]

Not for me it doesn't

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2		Product 1	10
3		Product 2	0
4		Product 3	6
5
6	Result	Day	44348	44349	44350	44351	44352	44353	44354	44355	44356	44357	44358	44359	44360	44361	44362	44363	44364	44365	44366	44367	44368	44369	44370	44371	44372	44373	44374	44375	44376	44377	44378
7		Day No	44348	44349	44350	44351	44352	44353	44354	44355	44356	44357	44358	44359	44360	44361	44362	44363	44364	44365	44366	44367	44368	44369	44370	44371	44372	44373	44374	44375	44376	44377	44378
8	Day of month	Product 1	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
9	Value	Product 2	24	25	24	17	8		22	18	18	0	0			0	14	18	21	10	10	8	21	21	10	23	17
10		Product 3	22		18	17	9	15	28	34	32	33	36	8		34	25	36	29	6		9	34	29	35	8	10	8	6
11
Main

Cell Formulas
Range	Formula
C2:C4	C2	=HLOOKUP(TODAY()-1,$C$7:$AG$10,MATCH(B2,$B$7:$B$10,0),0)
D6:AG7	D6	=C6+1
C7	C7	=C6

Do you have different products on each row?

 

[nparsons75]

That is very strange

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
2	Product 1	10
3	Product 2	10
4	Product 3	10
5
6	Day	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu
7	Day No	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
8	Product 1	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
9	Product 2	24	25	24	17	8		22	18	18	0	0			0	14	18	21	10	10	8	21	21	10	23	17
10	Product 3	22	27	18	17	9	15	28	34	32	33	36	8		34	25	36	29	6		9	34	29	35	8	10	8
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=HLOOKUP(TODAY()-1,$C$7:$AG$10,MATCH(B2,$B$7:$B$10,0),0)
D6:AG7	D6	=C6+1
C7	C7	=C6

 

[nparsons75]

Yes different products, this is just a sample of my document, my full spreadsheet has a lot more products (actually processes) I have tried to simplify to demonstrate how its working.

 

[Fluff]

Do you have calculation set to automatic or manual?

 

[nparsons75]

Ok I had it on manual....changed to auto and now it works....Thank you. Now, in my main file which I didnt share on here I need to replicate the fix. I have issues with it as it is slightly different but didn't want to share publicly.

I just need to figure out how it works, do the product names need to match exactly, is that what it is looking for?