Looking to Shorten Code

[dandj]

Hey all!

I was curious if there was any way to shorten this code. I want to be able to look at three columns (TYPE1, TYPE2, etc.) and essentially, in a "rank" order, have the value in the original cell be the one that first is shown in the column.

Here is what I am working with so far (and works, but don't feel like redoing everything):

Thanks!

 

[kweaver]

Welcome. You'll get more help (or any) if you use XL2BB to show a sample of your worksheet and expected results.

 

[Tetra201]

See if the following formula works for you:

=INDEX(Types,4-MOD(MAX((TYPE1=Items)*(Ranks+3),(TYPE2=Items)*(Ranks+2),(TYPE3=Items)*(Ranks+1)),10))

where
- Items is named range with your "Au", "FUCH", "FELD", ... , "CAL"
- Ranks is a named range with 80, 70, 60, ... , 10
- Types is a named range with "TYPE1", TYPE2", "TYPE3", =""