# The equation to obtain a constant value from several values for the same vehicle

#### Hany ali

##### Active Member
hello my Dear ,I want You Helping in this problem ,i Need any Formula to get the Same Result in Coulmn B ,in the Same Row for Number in Column C ...AS Following :
**If i have multiple Regions in Column A for the same Vehicle Record Nr from Column D , You should choose the farthest area ...Foe Example in my file for this Vehicle Record 460461 .it should be to choose between (MAK,SAF)I WILL CHOOSE "SAF"Because it's far destination
In general, it is required to find the farthest place in the same Vehicle ,if i have (SAF,MAK,SAHL,HUR)..I CHOOSE SAF
*In anothe case if i have (MAK,SAHL,HUR)..I CHOOSE MAK
*If i have (SAHL,HUR)In the same Vehicle .. i will choose SAHL
*If i have in the same Vehicle just (HUR).. I will choose it & This is generally for the entire area of the same car ,I mean else if i found onlt (SAHL OR MAK OR SAF) I will choose the region
thanks alot for all

#### Attachments

• 43.5 KB Views: 9
• 64.5 KB Views: 3

#### Excel Wizard

##### Member

=IF(D2=D1,"",INDEX({"SAF","MAK","SAHL","HUR"},MIN(MATCH(A2:INDEX(A2:A9,MATCH(2,1/(D2:D9=D2))),{"SAF","MAK","SAHL","HUR"},))))

Ctrl+Shift+Enter

#### Attachments

• 84 KB Views: 7

#### bosco_yip

##### Excel Ninja
Or, In B2, non-array formula copied down :

=IF(D2=D1,"",LOOKUP(1,0/MATCH({"HUR";"SAHL";"MAK";"SAF"},OFFSET(A2,,,COUNTIF(D:D,D2)),0),{"HUR";"SAHL";"MAK";"SAF"}))

Regards

#### Hany ali

##### Active Member
Excel Wizard
bosco_yip

Thank you very much to all of you, all are excellent solutions. The least thing is said about you in this case that you are creative..and it is really what is required of work.

#### Excel Wizard

##### Member
Another one, with array calculation but no need CTRL+Shift+Enter
B2

=IF(D2=D1,"",INDEX({"HUR","SAHL","MAK","SAF"},MAX(INDEX(MATCH(A2:A9,{"HUR","SAHL","MAK","SAF",0},)*(D2:D9=D2),))))