Matching First Letter of Cell to a Fixed Array Set

[dwgnome]

Hi,

Trying to get the following to work:

=LOOKUP(LEFT(B69,1),{"T","J","N","C","P"},{"Travel","Journalism","Nature","Creative","Pictorial"})

B69 contains TM, so the answer should be "Travel", but the formula above results in "Pictorial". The lookup cell will always have two letter codes (i.e., TM, TB, TI, PB, PI etc.).

Excel 2003,

Any suggestions?

Thanks,

 

[shg]

=INDEX({"Travel","Journalism","Nature","Creative","Pictorial"}, MATCH(LEFT(B69), {"T","J","N","C","P"}, 0))

or

=INDEX({"Travel","Journalism","Nature","Creative","Pictorial"}, FIND(LEFT(B69), "TJNCP"))

 

[mikerickson]

With LOOKUP, the first array has to be sorted ascending.
Try
=LOOKUP(LEFT(B69,1),{"C","J","N","P","T"},{"Creative","Journalism","Nature","Pictorial","Travel"})

 

[T. Valko]

Hi,

Trying to get the following to work:

=LOOKUP(LEFT(B69,1),{"T","J","N","C","P"},{"Travel","Journalism","Nature","Creative","Pictorial"})

B69 contains TM, so the answer should be "Travel", but the formula above results in "Pictorial". The lookup cell will always have two letter codes (i.e., TM, TB, TI, PB, PI etc.).

Excel 2003,

Any suggestions?

Thanks,

Try this...

Book1
	B	C	D	E	F
69	TM	Travel	_	T	Travel
70	_	_	_	J	Journalism
71	_	_	_	N	Nature
72	_	_	_	C	Creative
73	_	_	_	P	Pictorial
Sheet1

Create the 2 column table in E69:F73.

Then use this formula in C69:

=VLOOKUP(LEFT(B69),E69:F73,2,0)

 

[dwgnome]

Hi all,

Thank you for the different ways to the same solution. All of the suggestions worked!

However, as I do not like #NA when results are not found and I like to avoid the ISERROR(formula,"",formula) approach, I modified each contributor's suggestion as follows:

From:
=INDEX({"Travel","Journalism","Nature","Creative","Pictorial"}, FIND(LEFT(B69), "TJNCP"))

to

=INDEX({"","Travel","Journalism","Nature","Creative","Pictorial",""}, FIND(LEFT(B69), " TJNCP"))

From :
=LOOKUP(LEFT(B69,1),{"C","J","N","P","T"},{"Creative","Journalism","Nature","Pictorial","Travel"})

I modifed slightly to:

=LOOKUP(LEFT(B69,1),{"","C","J","N","P","T"},{"","Creative","Journalism","Nature","Pictorial","Travel"})

From:
=VLOOKUP(LEFT(B69),E69:F73,2,0)

I modified to: =IF(ISBLANK(B69),"",VLOOKUP(LEFT(B69),$AF$12:$AG$16,2,0))

 

[bosco_yip]

I modified to: =IF(ISBLANK(B69),"",VLOOKUP(LEFT(B69),$AF$12:$AG$16,2,0))

Or, modified to : =IF(B69="","",VLOOKUP(LEFT(B69),$AF$12:$AG$16,2,))

 

[T. Valko]

Hi all,

Thank you for the different ways to the same solution. All of the suggestions worked!

However, as I do not like #NA when results are not found and I like to avoid the ISERROR(formula,"",formula) approach, I modified each contributor's suggestion as follows:

From:
=INDEX({"Travel","Journalism","Nature","Creative","Pictorial"}, FIND(LEFT(B69), "TJNCP"))

to

=INDEX({"","Travel","Journalism","Nature","Creative","Pictorial",""}, FIND(LEFT(B69), " TJNCP"))

From :
=LOOKUP(LEFT(B69,1),{"C","J","N","P","T"},{"Creative","Journalism","Nature","Pictorial","Travel"})

I modifed slightly to:

=LOOKUP(LEFT(B69,1),{"","C","J","N","P","T"},{"","Creative","Journalism","Nature","Pictorial","Travel"})

From:
=VLOOKUP(LEFT(B69),E69:F73,2,0)

I modified to: =IF(ISBLANK(B69),"",VLOOKUP(LEFT(B69),$AF$12:$AG$16,2,0))

Good deal. Thanks for the feedback!