How can I make comparison between two text values with priority line over each other, in Excel?

user_14047543 0
Viewed 99

I have two lists , both full of these 4 text values : "Mainland", "Island ", "City" and "DP". Im trying to make a function that checks the combination of 2 in each row . But I reached a dead end, i cant think of how to do it.The priority given over eachother is : City < Mainland < Island < DP. An example would be :

enter image description here

Its two columns ("From" and "To") full of "Mainland", "Island", "City" and "DP" forming random combinations like shown above. I need a way to show in the "Category" column, in each row , the word that is superior than the other following the City < Mainland < Island < DP principle . eg Island is above City so in a combination of those 2, category shows "Island".

The idea is similar to 1<2<3 and it compares combinations of two at a time and puts in the cell the higher one

excel
2 Answers

In cell D2 paste the following formula:

=IF(MATCH(B2,{"City","Mainland","Island","DP"},)>=MATCH(C2,{"City","Mainland","Island","DP"},),INDEX({"City","Mainland","Island","DP"},MATCH(B2,{"City","Mainland","Island","DP"},)),INDEX({"City","Mainland","Island","DP"},MATCH(C2,{"City","Mainland","Island","DP"},)))

Now copy the formula downward.

However, a better way to do the same is to create a list somewhere on a sheet of the values in order. For example:

Z1 = City

Z2 = Mainland

Z3 = Island

Z4 = DP

Now, select Z1:Z4 and use the Name Box to name that range: Order

Now the formula can be shortened to:

=IF(MATCH(B2,Order,)>=MATCH(C2,Order,),INDEX(Order,MATCH(B2,Order,)),INDEX(Order,MATCH(C2,Order,)))

And a cleaner way still is to use the Name Manager (Ctrl-F3) to store the list as a named formula, Order:

={"City","Mainland","Island","DP"}

In this way you need not clutter up a worksheet with the ordered list definition.

user_35669980

You can use INDEX/AGGREGATE functions:

=INDEX({"City","Mainland","Island","DP"},AGGREGATE(14,0,(A2:B2={"City","Mainland","Island","DP"})*{1,2,3,4},1))

enter image description here

Edit:

If values are in non adjacent columns:

=INDEX({"City","Mainland","Island","DP"};AGGREGATE(14,0,(((B2={"City","Mainland","Island","DP"})+(F2={"City","Mainland","Island","DP"}))>0)*{1,2,3,4},1))
user_121888610
Related