How to find value with multiple tables with different column values?

[Bpuad]

I have a problem. I have multiple tables and want to find a value. So far easy but my problem is that I have different values (dates in this case) in the columns. See attached picture.


So what I want to do it to find the value of a stock based on the name of the stock and the date. I have tried but just can't seem to find a solution .

This is the sheet I want the values in (it's all in the same file)

And this is the sheet where the values is

 

[Toadstool]

Hi Bpuad,

I assume all stock tables are the same format.

1. Can you confirm all stock names will be above the Open price?
2. Can you confirm how to calculate the Value, or do you just want opening price?
3. Can you use XL2BB, or paste as text, the sample tables for three stocks so retyping the image can be avoided?
4. I'll provide an Excel 2016 solution but can you update your profile with your version details?

 

[Bpuad]

Hi Bpuad,

I assume all stock tables are the same format.

1. Can you confirm all stock names will be above the Open price?
2. Can you confirm how to calculate the Value, or do you just want opening price?
3. Can you use XL2BB, or paste as text, the sample tables for three stocks so retyping the image can be avoided?
4. I'll provide an Excel 2016 solution but can you update your profile with your version details?

Hi,

1. yes same format but different number of rows and dates.
2. I want closing price.
3. Don't know what that is or how to use it? Will have a look at YouTube.
4. Will do, I have MS office Professional plus 2019

 

[Bpuad]

Hi,

so let's give it a try . This is the sheet with all the data

Morris Excel Dashboard.xlsx
	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
1				1		Boozt						2		Eolus Vind						3		FM Mattsson
2				Boozt	Date	Open	High	Low	Close	Volume		Eolus Vind	Date	Open	High	Low	Close	Volume		FM Mattsson	Date	Open	High	Low	Close	Volume
3				1463	2020-12-01	175,4	178,4	166,4	172,8	1 314 755		291	2020-12-03	205,4	206,4	195,2	198,7	213 688		1425	2020-12-08	167,5	170	164	164	3 201
4				1463	2020-12-02	173	174	161,6	165,2	984 525		291	2020-12-04	198	202,2	190,4	196,2	227 152		1425	2020-12-09	163	169,5	161,5	161,5	1 635
5				1463	2020-12-03	165,4	166,2	155	155,2	397 274		291	2020-12-07	196,2	200	193,1	195,7	85 738		1425	2020-12-10	161,5	167	161,5	162,5	1 906
6				1463	2020-12-04	155	159,4	152,8	156	297 394		291	2020-12-08	196	199,7	194,1	195,9	110 420		1425	2020-12-11	164	169,5	164	167	3 048
7				1463	2020-12-07	161	166,4	157,4	159,8	651 866		291	2020-12-09	197,5	203,8	196,5	199,8	111 990
8				1463	2020-12-08	160,6	161,6	154,6	157,4	288 504		291	2020-12-10	200,4	202,4	188,7	193,5	259 575
9				1463	2020-12-09	157,4	160,2	155,8	156,4	241 343		291	2020-12-11	193,9	193,9	189,5	192,9	81 907
10				1463	2020-12-10	158,6	158,8	154,8	157,4	242 274
11				1463	2020-12-11	158	159,6	155,6	156,2	199 661
12
13
14
15
16
Data_stocks

Cell Formulas
Range	Formula
F1,V1,N1	F1	=IFERROR(D2,"")
D2	D2	=IFERROR(BD_STOCKPRICE(INDEX(INST!$B$3:$O$5000,MATCH(INDEX(Dashboard!$B$12:$C$26,MATCH(Data_stocks!D1,Dashboard!$B$12:$B$26,0),2),INST!$C$3:$C$5000,0),1),INDEX(Dashboard!$B$11:$L$26,MATCH(Data_stocks!D1,Dashboard!$B$11:$B$26,0),3),INDEX(Dashboard!$B$11:$L$26,MATCH(Data_stocks!D1,Dashboard!$B$11:$B$26,0),4),"d3:j4999"),"")
L2	L2	=IFERROR(BD_STOCKPRICE(INDEX(INST!$B$3:$O$5000,MATCH(INDEX(Dashboard!$B$12:$C$26,MATCH(Data_stocks!L1,Dashboard!$B$12:$B$26,0),2),INST!$C$3:$C$5000,0),1),INDEX(Dashboard!$B$11:$L$26,MATCH(Data_stocks!L1,Dashboard!$B$11:$B$26,0),3),INDEX(Dashboard!$B$11:$L$26,MATCH(Data_stocks!L1,Dashboard!$B$11:$B$26,0),4),"l3:r4999"),"")
T2	T2	=IFERROR(BD_STOCKPRICE(INDEX(INST!$B$3:$O$5000,MATCH(INDEX(Dashboard!$B$12:$C$26,MATCH(Data_stocks!T1,Dashboard!$B$12:$B$26,0),2),INST!$C$3:$C$5000,0),1),INDEX(Dashboard!$B$11:$L$26,MATCH(Data_stocks!T1,Dashboard!$B$11:$B$26,0),3),INDEX(Dashboard!$B$11:$L$26,MATCH(Data_stocks!T1,Dashboard!$B$11:$B$26,0),4),"t3:z4999"),"")

Named Ranges
Name	Refers To	Cells
Stock_1	=Data_stocks!$D$1:$J$1999	D2
Stock_2	=Data_stocks!$L$1:$R$1999	L2
Stock_3	=Data_stocks!$T$1:$Z$1999	T2

And this is the sheet where I want to sum all data

Morris Excel Dashboard.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2	Date	Boozt	Eolus Vind	FM Mattsson	G5 Entertainment	Gaming Innovation	GARO	Invisio	Pricer	Studsvik	Xvivo Perfusion
3	2020-12-01
4	2020-12-02
5	2020-12-03
6	2020-12-04
7	2020-12-07
8	2020-12-08
9	2020-12-09
10	2020-12-10
11	2020-12-11
12	2020-12-14
13	2020-12-15
14	2020-12-16
15	2020-12-17
16	2020-12-18
17	2020-12-21
18	2020-12-22
19	2020-12-23
20	2020-12-24
21	2020-12-25
22	2020-12-28
23	2020-12-29
24	2020-12-30
25	2020-12-31
Total

Cell Formulas
Range	Formula
B2:K2	B2	=IF(OFFSET(Data_stocks!$F$1,0,(COLUMN()-2)*$C$1)=0,"",OFFSET(Data_stocks!$F$1,0,(COLUMN()-2)*$C$1))
A3	A3	=MIN(Dashboard!$D$12:$D$104)
A4:A25	A4	=WORKDAY(A3,1)

 

[Toadstool]

I had a problem with the formulae as a couple of tabs were missing so I just pasted the values provided.

The formula for B3 can be copied across and down. You may want to modify the ranges used to best fit your data size:
=IFERROR(INDEX(Data_stocks!$A$1:$ZZ$9999,MATCH($A3,OFFSET(Data_stocks!$A$1,,MATCH(B$2,Data_stocks!$2:$2,0),9999),0),(MATCH(B$2,Data_stocks!$2:$2,0)+1)+4),"")

 

[Bpuad]

I had a problem with the formulae as a couple of tabs were missing so I just pasted the values provided.

The formula for B3 can be copied across and down. You may want to modify the ranges used to best fit your data size:
=IFERROR(INDEX(Data_stocks!$A$1:$ZZ$9999,MATCH($A3,OFFSET(Data_stocks!$A$1,,MATCH(B$2,Data_stocks!$2:$2,0),9999),0),(MATCH(B$2,Data_stocks!$2:$2,0)+1)+4),"")

Works like a charm , thanks

 

[Toadstool]

You're welcome!
Thanks for the feedback.