Vlookup for part Lookup_value

[Peter]

All,

Scenario:

My Table_array has 10 different countries in column A (eg, United Kingdom),
10 different, respective, three lettered currency abbreviations in column B
(eg, GBP).

I need to be able to enter the first five letters only, of one of the
countries into cell C1 (which would represent the Lookup_value) and get cell
D1, which has the VLoopup formula, to throw up the column B's respective
three letter currency abbreviation.

Eg,

Col A Col B Col C Col D

Country Currency enter 5 letters VLookup result

United Kingdom GBP unite GBP
Japan JPY
France EUR
Germany EUR

I've tried =VLOOKUP(C1,A1:B4,2,FALSE) with no joy.
Basically, I need a lookup formula that will find a value from a part-value
search.

Thanks.
Peter

 

[Frank Kabel]

Hi
try the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(B1:B100,MATCH(C1,LEFT(A1A100,5),0))

 

[Peter]

Hi Frank,
What is this procedure of applying the 'CTRL+SHIFT+ENTER' within these
instructions?

Thanks.
Peter

 

[Peter]

Frank,

I've been using my ignorance to work on this for ages, now, thanks to you, I
can put it to rest.

Much appreciated.
Peter

 

[RagDyer]

Peter,

FWIW,
Your original formula would work, with a slight modification, without
needing to be an array formula.

Just add
&"*"

=VLOOKUP(C1&"*",A1:B4,2,FALSE)

You could also get away with typing *less* then 5 characters.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Frank,

I've been using my ignorance to work on this for ages, now, thanks to you, I
can put it to rest.

Much appreciated.
Peter