Vlookup/Match?? Help please

[krosado]

I have two seperate worksheets
Worksheet one has all of my data.

In worksheet two, I want to a vlookup for a word that happens to be repeated several times, but the second column shows the difference.

ex: Worksheet 1
A1 b1
1 Dog Lab
2 Bird Parakeet
3 Dog Spaniel
4 Dog Boxer

I want a formula that if "Dog" then B1
But a list.

My vlookup currently just shows the first row and does not acknowledge rows 3 or 4 when I cut and paste the formula.

Please help!

 

[Aladin Akyurek]

Thank you!

But it's still not working for me. In my array "Animal" I have some blanks.
Would that cause the problem? My cell that I want the "types of dogs" is saying "#value".

Did you apply the control+shift+enter correctly? You need to press down the control and shift keys together while you hit the enter key. if done properly, you'll see a pair of { and } appear around the formula on the Formula Bar.

Try to rebuild the exhibits (see post #3) first in order to see how the approach works, then try to adapt it to your workbook.

 

[T. Valko]

Thank you!

But it's still not working for me. In my array "Animal" I have some blanks.
Would that cause the problem? My cell that I want the "types of dogs" is saying "#value".

Post the formula that you are trying to use. If you're using named ranges also include the addresses of those ranges.

Also, the formula to list the types is a special kind of formula that needs to be entered in a different manner than a regular formula.

The formula to list the types is an ARRAY formula.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.