How to create a vlookup between tables to retrieve a text field

[Lobojo]

Hi,

I'm trying to create a column in Table 1 that would be filled via a "vlookup" style from Table 2 or Table 3, depending on the 1st value in column # (if 4 -> table 2, if 2 -> table 3)

Which is the proper command to be used and how have to be connected the 3 tables in order that it works properly? I've tried various way without any correct result

I've tried with:

TEXT111 = SWITCH (

    TRUE (),

    LEFT('Table1'[#],1)=6 , RELATED ('Table3'[Text1]),

    LEFT('Table1'[#],1)=4, RELATED ('Table2'[Text1])

)

or with

text1 = switch(true,left('Table1'[#],1)=4,calculate(SELECTEDVALUE(Table2[Text1])),left('Table1'[#],1)=6,calculate(SELECTEDVALUE(Table3[Text1])))

but it doesn't work

Thanks!

Table 1			Table 2				Table 3
#	Text		#4	Text1			#2	Text1
4100			4100	Pippo			2400	Minnie
4200			4200	Pluto			2500	Topolino
2400
2500

[FreemanZ]

hi @Lobojo 

if Table1 is related with Table2 and Table3, then just add a column in Table1 like:

Text = RELATED(Table2[Text1])&RELATED(Table3[Text1])

it worked like:

[Lobojo]

Correct. But the table have to be related 1:1 in order to work. Thanks

[FreemanZ · Accepted solution]

or you try like:

Text2 = 
MINX( FILTER(Table2, Table2[#4] = Table1[#]), [Text1] )
&
MINX( FILTER(Table3, Table3[#2] = Table1[#]), [Text1] )

This approach does not rely on relationship.

[FreemanZ]

or you try like:

Text3 = 
LOOKUPVALUE(Table2[Text1], Table2[#4], Table1[#])
&
LOOKUPVALUE(Table3[Text1], Table3[#2], Table1[#])

This approach does not rely on relationship as well.