Vlookup ...guess I have a mental block

[brittany1]

I have been trying to figure this out.. just aint working for me

I have two columns. Both sorted.

Column a is the short list
Column b is the lont list.

I want to check if each cell in column a short list, residents anywhere in column b long list.
If it does, I want to print column b's match in column c. (I know the example isnt sorted .. just shown for illustration)

So column a might have
dog
cat
bird
monkey

Column b might have
pig 23
horse 93
dog 78
monkey PP
zebra LZ
cat 92
tiger 429
bird 893
goat 777

Results needed in column c
dog 78
cat 92
bird 893
monkey PP

Thanks for the help .. been racking my brain on this... havent found a REALLY clear doc that explains Vlookups.

 

[SkipVought]

I'd use MATCH()
[tt]
=match(a1,b:b,0)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Although you could use INDEX and MATCH, you could also use VLOOKUP with a wildcard character:
=VLOOKUP(A1 & "*",B:B,1,FALSE)

In this case, you are looking up a value in a one-column list in column B. You want an exact match (rather than approximate), so the fourth parameter needs to be FALSE. The first parameter puts the asterisk wildcard character after the text in cell A1, so any (or no) characters following "dog" would constitute a match.

If you want to require a space between dog and the following characters, then you would modify the formula with a space before the asterisk like this:
=VLOOKUP(A1 & " *",B:B,1,FALSE)