If statement? Or lookup? Or both? Please help...

[ian123]

Can anyone help me do the following...

I have a long list of names in column A and corresponding numeric
values in B of sheet 1.

These values relate to different categories and i want to split the
original list in sheet 1 into 5 sheets depending on the category.

Imagine the list consists of the following...

Dog 100
Doggy 50
Doggie 150
Kitten 75
Kitty 100
Kittens 120
Bird 90
Birdie 110
Birdy 25


I want to split the list into three category "Dog", "Kit" and "Bird".

Is it possible to put a formula in sheet 2 to search the original list
in sheet 1 and pull all of the entries containing the word dog (in this
case 'dog, doggy, doggie' and any other variations featuring 'dog') and
the respective values from sheet 1 into the second sheet.

Many thanks for your help

 

[Jim]

Hi All:

Lookup tables are far easier to use. In fact, you will find that in order to solve your problem you will need nested ifs. Nesting ifs gets ugly quick. Therefore, I recommend using the vlookup table.

I hope that helps,

Jim

 

[ian123]

I've only ever used vlookups to search for exact matches - is i
possible to search for entries that are partial matches or contain som
of the words?

Eg, if i vlookup dog all i get back is the appropriate value for a
exact match of dog... however i need the lookup to pick up any matche
containing dog eg dog, doggy, doggie, dog123 etc

Anyone know if this is possible? Cheer

 

[Debra Dalgleish]

You could use an Advanced Filter to extract the lists. There are
instructions here:

http://www.contextures.com/xladvfilter01.html#ExtractWs

In the criteria area use a heading that matches the heading in the table
(e.g. Animal), and in the cell below, enter the animal name --

Animal
Kit

Follow the instructions to 'Extract Data to Another Worksheet'

 

[Dave Peterson]

I didn't go back to the original post. My suggestion will pick up the first
match (and only the first match).

each cell that holds that vlookup formula will return 100 (from the sample
data). Don't think vlookup is a viable approach to return multiple matches.

 

[Dave Peterson]

Tom corrected this response.

If you're tring to add the values returned by the =vlookup()'s, you could use
the userdefined function in this thread:

http://groups.google.com/[email protected]

And then wrap it in =sum()

=sum(mvlookup("*dog*",sheet2!$a:$b,2,false))

If you're returning text or want to see the individual values, you can select as
many cells (in a row or in a column) that you expect to return and then hit
ctrl-shift-enter (instead of just enter).