indent formula or macro

**arnab0711** · 03-18-2010, 01:36 AM

Hi,
I want to create a macro or a formula which will
create the following formula.
if first 2 letters in column 'H' items codes contains
BT then it will show BTS and likewise if
MW then Minilink / Access Link
BS then BSC
MS then MSC
IN then IN
TE then Transmission
TS then Transmission
NM then OSS
SE then Services
AN then GSM antenna
NL then NLD-OFC
MP then MPBN
VA then VAS
TW then Civil & Infra
ST then Civil & Infra
DG then Civil & Infra
TF then Civil & Infra
IF then Civil & Infra
PP then Civil & Infra
BA then Civil & Infra
AC then Civil & Infra
IN then Civil & Infra
SH then Civil & Infra
PS then Civil & Infra
OF then NLD-OFC
NW then VAS
gp then VAS
UP then Civil & Infra
BS then BSC
BA then Civil & Infra
AC then Civil & Infra

and lastly if column I has a "service" word in it it will show "services" as well

**teylyn** · 03-18-2010, 01:49 AM

Hi,
put your codes and the full text values into a table, then use something like

=vlookup(left(h1,2),$a$1:$B$30,2,false)

with your lookup table in A1:B30.

Adjust ranges to suit. Copy down.

**arnab0711** · 03-18-2010, 03:43 AM

I know this can be done through a lookup,but I want a formula or macro only.

**teylyn** · 03-18-2010, 03:54 AM

You have to keep your data somewhere. If you do this with a macro, hard-coding all the data pairs and some of your value pairs change, then you'll have to edit the macro. Embedding all these pairs into a formula would be even less maintainable. You don't have to have the table on the same sheet. It can be on a hidden sheet, or in another workbook alltogether, FWIW. Vlookup works with closed workbooks, too!