I have a Data table where Departure and Arrival code is in each column (Eks Column 1; LONDON2085 , Column 2; BIRMINGHAM1655)
I have a separate Lookup table where ONE code is mentioned and a type , (Eks Column 1 ; LONDON2085 , Column 2 ; "Within City perimeter"
The purpose is to use Column 2 and the type, whenever a departure and arrival code is equal to the value in Column 1, Eks (Departure : LONDON2085, Arrival ; LONDON2085 - gives value "Within City perimeter"
The problem is that RELATE looks in the whole Data table, but i need to RELATE to look up in both Departure and then Arrival code before returning from Lookup table.
I solved it somehow, I split up the Departure and Arrival codes into two identical tables: but encountered a new problem, i use the formula :
IF(AND(RELATED(Arrival_Lookuptable[Planetzone])=TRIM([Arrival_Datatable]);RELATED(Departure_Lookuptable[Planetzone])=TRIM([Departure_Datatable]));1;0) ,
But somehow it still counts empty cells as well. even though they are not listed in the Tables, what is wrong?
I have a separate Lookup table where ONE code is mentioned and a type , (Eks Column 1 ; LONDON2085 , Column 2 ; "Within City perimeter"
The purpose is to use Column 2 and the type, whenever a departure and arrival code is equal to the value in Column 1, Eks (Departure : LONDON2085, Arrival ; LONDON2085 - gives value "Within City perimeter"
The problem is that RELATE looks in the whole Data table, but i need to RELATE to look up in both Departure and then Arrival code before returning from Lookup table.
I solved it somehow, I split up the Departure and Arrival codes into two identical tables: but encountered a new problem, i use the formula :
IF(AND(RELATED(Arrival_Lookuptable[Planetzone])=TRIM([Arrival_Datatable]);RELATED(Departure_Lookuptable[Planetzone])=TRIM([Departure_Datatable]));1;0) ,
But somehow it still counts empty cells as well. even though they are not listed in the Tables, what is wrong?
