Looking up part of a cell in a table

[Jayde]

Hi,

I have to perform a v-lookup to return a value but the problem is the cell I am looking up begins with but does not exactly match the the data in the table.
For example I want to look up sheet 1 cell A1 which is "Compass" in the table in sheet 2 column A and then return the associated value in column B. It is referenced as "Compass Group" in the table so I do not get a value returned if I use the exact matching function of the vlookup (i.e. using "false") but if I use the approx it one (i.e. "true") then it returns "Compagnie" which is the name listed before "Compass Group".
Basically I want to perform a vlookup function using a cell that begins with but not exactly matches a sting of letters in a table. Can anyone help?
Thanks!

 

[adam087]

Try

=INDEX(Sheet2!$B$1:$B$5000, MATCH(Sheet1!$A$1&"*", Sheet2$A$1:$A$5000, 0), 1)

Regards
Adam