Formula Simplifications Using Nested If, Indirect & Address Functi

[lmavroff]

After reviewing some previous posts, I've come up with the following
function. Ideally, I would like to give it a Name reference, but it is too
long. Any suggestions for simplifying and shortening?

The formula looks at the left adjacent cell for location codes that
correspond to location descriptions. The column locations have the potential
of changing so I needed a solution that could dynamically change without a
fixed reference point. Let me know if you need any other information. Thank
you in advance for any assistance.

=IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="O","Regional / Highland
Ave",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OMV","Maryville / Armory
Place",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OWK","West / Cedar
Bluff",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OSV","Sevierville / Bradford
Square",INDIRECT(ADDRESS(ROW(),COLUMN()-1))))))

 

[Dave Peterson]

Select B1 and then use this formula in the Insert|Name|Define dialog:

=IF(A1="O","Regional / Highland Ave",
IF(A1="OMV","Maryville / Armory Place",
IF(A1="OWK","West / Cedar Bluff",
IF(A1="OSV","Sevierville / Bradford Square",A1))))

Then give it a nice name like ExpandName
and test it.

=expandname

In any cell you want (not in column A). And the type 0 or OMV or ... in the
cell to the left.

 

[Brad]

Based on what I understand the following formula will work for you.
=IF(ISNA(MATCH(B10,$D$4:$D$7,0)),B10,INDEX($E$4:$E$7,MATCH(B10,$D$4:$D$7,0)))

This assumes the formula is in cell c10
This assumes the "Codes of "O" thru "OSV" are in cells D47
This assumes the Description of these codes are in cells E4:E7

You can copy the above equation to the right of any cell that has the "Code"
you want to evaluate and it will do it correctly.

If you add to the "Code" you will have to expand the ranges (of D47 and
E4:E7) appropriately (do you know what that means?)

 

[Roger Govier]

Hi

Create 2 named ranges
Codes with the values O, OMV,OWK and OSV
Names as 2 columns including the column for Codes and a column alongside
with the required values for each code
Then use
=IF(COUNTIF(codes,INDEX($1:$65536,ROW(),COLUMN()-1)),
VLOOKUP(INDEX($1:$65536,ROW(),COLUMN()-1),Names,2,0),
INDEX($1:$65536,ROW(),COLUMN()-1))

A bit shorter, not as many function calls and no use of volatile
functions, so it should be quicker.
You could created named formula called Result, and paste the above
formula into it.

On your sheet, then just use
=result