Vlookup Help

[Jason]

I have the following text in:

Column A Column B Column C
Pinnacle Capital North Pinnacle 100%

I would like to utilize (unless there's a better
alternative) a vlookup function to lookup the FIRST word
in Column A ("Pinnacle"), look to column B
for "Pinnacle", and return the value in colum C. The
problem is that the when looking to Column B to find a
match, "Pinnacle" may not be text by itself, but may be
included within other text. Is there a way to
lookup "Pinnacle" in A, have it look through a string of
text in B to match only that word, and have it return the
value in C based on the previous? I would be looking for
an exact match of the first word in column A. Thanks.

 

[Vasant Nanavati]

Something like:

=VLOOKUP((LEFT(A1,FIND(" ",A1)-1)),B1:C100,2,FALSE)

 

[Max]

One way ..:

In Sheet2
------------
Cols A and B will contain the text phrases, for example:
"Pinnacle Capital" in A1,
"North Pinnacle" in B1
and so on

Assume the first word 'Pinnacle" in col A is the lookup_value
and the lookup table for 'Pinnacle" is found in Sheet1!A:B
where col A would contain: "Pinnacle",
with col B containing : 100%, and so on

Put in C1 (in Sheet2):

=IF(ISERROR(FIND(LEFT(TRIM(A1),SEARCH("
",TRIM(A1))-1),B1)),"",VLOOKUP(LEFT(TRIM(A1),SEARCH("
",TRIM(A1))-1),Sheet1!A:B,2,0))

Format col C as percentage
Copy C1 down

 

[Biff]

Here's another method that doesn't require changing the
lookup value from Pinnacle Capital to just Pinnacle:

A1 = lookup value = Pinnacle Capital
B1 = North Pinnacle
C1 = 100

=SUMPRODUCT(--(COUNTIF(A1,"*"&LEFT(A1,FIND(" ",A1)-1)&"*")

0),--(COUNTIF(B1,"*"&LEFT(A1,FIND(" ",A1)-1)&"*")>0),C1)

Kinda clunky but it can be used as both a "lookup" and a
two dimensional "sumif".

Biff