Multiple VLOOKUP formulas in ONE cell

[guscy]

Hi,

I am trying to organize a weekly work schedule. Working hours are standard each week. I would like to select between T_1 and T_2 in cell B2, B3, B4..., and automatically cells C2:I2 auto fill with working hours according tables. Selection T_1 is for table 1 and selection T_2 for table 2.

I manage to have it work only for T_1. If I select T_2 I get FALSE.

I would appreciate your help!

Thanks

Book1
	A	B	C	D	E	F	G	H	I
1		SELECT TABLE	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
2	NAME 1	T_1	7:00-15:00	DAY OFF	13:30-21:30	9:00-16:30	9:00-16:30	8:00-16:00	9:00-15:00
3	NAME 2	T_1	7:00-15:00	DAY OFF	13:30-21:30	9:00-16:30	9:00-16:30	8:00-16:00	9:00-15:00
4	NAME 3	T_2	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5
6	TABLE 1			TABLE 2
7	T_1			T_2			TABLES
8	MONDAY	7:00-15:00		MONDAY	7:00-15:00		T_1
9	TUESDAY	DAY OFF		TUESDAY	9:00-16:30		T_2
10	WEDNESDAY	13:30-21:30		WEDNESDAY	13:00-21:30
11	THURSDAY	9:00-16:30		THURSDAY	9:00-15:30
12	FRIDAY	9:00-16:30		FRIDAY	9:00-16:00
13	SATURDAY	8:00-16:00		SATURDAY	9:00-16:30
14	SUNDAY	9:00-15:00		SUNDAY	DAY OFF
Sheet1

Cell Formulas
Range	Formula
C2	=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(C1,T_1,2,IF($B$2="T_2",VLOOKUP(C1,TBL_S_2,2,FALSE)))))
C3	=IF(ISBLANK($B$3)," ",IF($B$3="T_1",VLOOKUP(C1,T_1,2,IF($B$3="T_2",VLOOKUP(C1,T_2,2,FALSE)))))
C4	=IF(ISBLANK($B$4)," ",IF($B$4="T_1",VLOOKUP(C1,T_1,2,IF($B$4="T_2",VLOOKUP(C1,T_2,2,FALSE)))))
D2	=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(D1,T_1,2,IF($B$2="T_2",VLOOKUP(D1,TBL_S_2,2,FALSE)))))
D3	=IF(ISBLANK($B$3)," ",IF($B$3="T_1",VLOOKUP(D1,T_1,2,IF($B$3="T_2",VLOOKUP(D1,T_2,2,FALSE)))))
D4	=IF(ISBLANK($B$4)," ",IF($B$4="T_1",VLOOKUP(D1,T_1,2,IF($B$4="T_2",VLOOKUP(D1,T_2,2,FALSE)))))
E2	=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(E1,T_1,2,IF($B$2="T_2",VLOOKUP(E1,TBL_S_2,2,FALSE)))))
E3	=IF(ISBLANK($B$3)," ",IF($B$3="T_1",VLOOKUP(E1,T_1,2,IF($B$3="T_2",VLOOKUP(E1,T_2,2,FALSE)))))
E4	=IF(ISBLANK($B$4)," ",IF($B$4="T_1",VLOOKUP(E1,T_1,2,IF($B$4="T_2",VLOOKUP(E1,T_2,2,FALSE)))))
F2	=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(F1,T_1,2,IF($B$2="T_2",VLOOKUP(F1,TBL_S_2,2,FALSE)))))
F3	=IF(ISBLANK($B$3)," ",IF($B$3="T_1",VLOOKUP(F1,T_1,2,IF($B$3="T_2",VLOOKUP(F1,T_2,2,FALSE)))))
F4	=IF(ISBLANK($B$4)," ",IF($B$4="T_1",VLOOKUP(F1,T_1,2,IF($B$4="T_2",VLOOKUP(F1,T_2,2,FALSE)))))
G2	=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(G1,T_1,2,IF($B$2="T_2",VLOOKUP(G1,TBL_S_2,2,FALSE)))))
G3	=IF(ISBLANK($B$3)," ",IF($B$3="T_1",VLOOKUP(G1,T_1,2,IF($B$3="T_2",VLOOKUP(G1,T_2,2,FALSE)))))
G4	=IF(ISBLANK($B$4)," ",IF($B$4="T_1",VLOOKUP(G1,T_1,2,IF($B$4="T_2",VLOOKUP(G1,T_2,2,FALSE)))))
H2	=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(H1,T_1,2,IF($B$2="T_2",VLOOKUP(H1,TBL_S_2,2,FALSE)))))
H3	=IF(ISBLANK($B$3)," ",IF($B$3="T_1",VLOOKUP(H1,T_1,2,IF($B$3="T_2",VLOOKUP(H1,T_2,2,FALSE)))))
H4	=IF(ISBLANK($B$4)," ",IF($B$4="T_1",VLOOKUP(H1,T_1,2,IF($B$4="T_2",VLOOKUP(H1,T_2,2,FALSE)))))
I2	=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(I1,T_1,2,IF($B$2="T_2",VLOOKUP(I1,TBL_S_2,2,FALSE)))))
I3	=IF(ISBLANK($B$3)," ",IF($B$3="T_1",VLOOKUP(I1,T_1,2,IF($B$3="T_2",VLOOKUP(I1,T_2,2,FALSE)))))
I4	=IF(ISBLANK($B$4)," ",IF($B$4="T_1",VLOOKUP(I1,T_1,2,IF($B$4="T_2",VLOOKUP(I1,T_2,2,FALSE)))))

Named Ranges
Name	Refers To	Cells
T_1	=Sheet1!$A$8:$B$14
T_2	=Sheet1!$D$8:$E$14

 

[Aladin Akyurek]

Have a look at the INDIRECT function.

 

[ghrain22]

It looks like you're missing a parameter and parenthesis
=IF(ISBLANK($B$2)," ",IF($B$2="T_1",VLOOKUP(C1,T_1,2,False),IF($B$2="T_2",VLOOKUP(C1,TBL_S_2,2,FALSE)))))

However, This is a good use for the indirect formula instead of IF's. The indirect formula basically converts a string into a reference
=Iferror(Vlookup(C1,Indirect($B2),2,false)),"")

So if B2 contains "T_2" then
Indirect(B2)
Will look at the range T_2

 

[guscy]

Really thanks!!!

Formula with IF, worked!

What about INDIRECT function? What is the formula?

 

[CharlesChuckieCharles]

with INDIRECT

=IF(ISBLANK($B2)," ",VLOOKUP(C1,INDIRECT(B2),2,FALSE))

 

[ghrain22]

Really thanks!!!

Formula with IF, worked!

What about INDIRECT function? What is the formula?

Indirect converts a string into an actual range
So if A1 contains: T_1
then
Indirect(A1)
looks at the named range "T_1"

 

[guscy]

Ok thanks! I will use formula with INDIRECT because is more easy. I had no idea about it!

 

[guscy]

Indirect converts a string into an actual range
So if A1 contains: T_1
then
Indirect(A1)
looks at the named range "T_1"

Thanks for the explain how INDIRECT works.