Vlookup duplicate with aproximate values

[GiuGee]

Hello,

I have a table with KM and SB and I need to know the subdivision, according to another table

Trem	KM	SB
H17	170,1	LEB
D22	133,6	LAR
W03	301,302	ZSN
H21	81,526	NPV
U25	152,5	LGI
U25	170,1	LEB
H99	83,851	NOR
H37	223,109	NJO
Z15	338,57	NCN

Equipamento	KMI	KMF	Subdivisão	NomeSubdivisão	Estação
9/170400-170760tg	170,4	170,76	9	IGUAÇU - UVARANAS	LEB
9/170760-170803cv	170,76	170,803	9	IGUAÇU - UVARANAS	LEB
9/170803-170880tg	170,803	170,88	9	IGUAÇU - UVARANAS	LEB
20/013040-013150tg	13,04	13,15	20	CORVO - ESTRELA	NOR
21/079554-079913cv	79,554	79,913	21	GENERAL LUZ - ROCA SALES	NPV
21/079913-080710tg	79,913	80,71	21	GENERAL LUZ - ROCA SALES	NPV
21/080710-081371cv	80,71	81,371	21	GENERAL LUZ - ROCA SALES	NPV
21/081371-082217tg	81,371	82,217	21	GENERAL LUZ - ROCA SALES	NPV
21/082217-082500cv	82,217	82,5	21	GENERAL LUZ - ROCA SALES	NPV
21/082800-083220cv	82,8	83,22	21	GENERAL LUZ - ROCA SALES	NOR
21/083220-084200tg	83,22	84,2	21	GENERAL LUZ - ROCA SALES	NOR
77/300000-300100cv	300	300,1	77	RUBIAO JUNIOR - BAURU	ZSN
77/300100-300500tg	300,1	300,5	77	RUBIAO JUNIOR - BAURU	ZSN
77/300500-300745cv	300,5	300,745	77	RUBIAO JUNIOR - BAURU	ZSN
77/300745-300860tg	300,745	300,86	77	RUBIAO JUNIOR - BAURU	ZSN

I want to know the name of the equipment and the Subdivisão. The KM in the first table is between the values of the second table.

Sorry my english. It is not my native language.

Someone can help me?

[Anonymous]

Once a relationship is created between two tables, add a new column in second table using the following dax:

=Related(FirstTableName[ColumnName])

In this case, I assume that KM is & KMI are related between the two fields. 

[GiuGee]

I can't relate the 2 tables, because there are no columns with unique values.

The KM of the first table must be >= KMI and <KMF from the second table

[Anonymous]

You can create a column using IF or Lookupvalue statements too.

I would not do it justice to list the options here but see

IF Lookup with lookup table 

lookup table

[Zubair_Muhammad]

@GiuGee

You can use this column in Table 1 to get all equipments in Table 2 that fall in the range

Column =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Table2[Equipamento] ),
        FILTER ( Table2, Table1[KM] >= [KMI] && Table1[KM] <= [KMF] )
    ),
    [Equipamento],
    ", "
)

or you can use this column if you want any of the matching equipments

Column 2 =
CALCULATETABLE (
    FIRSTNONBLANK ( Table2[Equipamento], 1 ),
    FILTER ( Table2, Table1[KM] >= [KMI] && Table1[KM] <= [KMF] )
)

[Zubair_Muhammad · Accepted solution]

@GiuGee

Please see file attached

[GiuGee]

This has been very helpfull.

But it's missing the SB as parameter

The "trem" H99 "SB" NOR should have the "Equipamento" 21/083220-084200tg but it's bringing 77/300500300745cv instead.

SB it's the location and must the same in both tables.

How can I look between de Km's and on the exact location?