Need data cross-referenced in multiple tables: VLOOKUP vs. Match vs. Index

[James McMurray]

I've got a sheet whose structure isn't the best, but that I c can't change without causing ripples that break lots of other stuff. In one area, several items from various categories are listed, and I need to sort those out and use their locations in another page to get some descriptive data. Unfortunately, the categories the objects fall in aren't defined in a central spot.

For example, if the data is

Animals

Please Login or Register  to view this content.

It wouldn't be a problem except for those pesky mammals, which are defined in another sheet as being cats, dogs, or horses. For instance:

Please Login or Register  to view this content.

* I can't just use the full table, because there are a lot of rows I don't want to pick up, like this one.

I need to somehow convert that data into a single sheet listing cats, dogs, and horses along with their other traits like color. I had the idea of adding another column to the first table which would tell the animal's actual type (pulled via lookup from the third table). Then I'd use that row to grab the animals one at a time. For instance:

Please Login or Register  to view this content.

I could then use that column to construct my subsets.
Dogs

Please Login or Register  to view this content.

Until I've exhausted the possibilities (there will never be more than 15 dogs, so I can just search 15 times and leave blanks where needbe.

Unfortunately, the distinction between vlookup, hlookup, match, and index has me unsure which to use where.

Any help would be greatly appreciated.

[NBVC]

Please read our forum rules. Then please update your title according to our Rule # 1.

[James McMurray]

Sorry about that. It's fixed.

I figured out the first half, and now have a table similar to

Please Login or Register  to view this content.

Can anyone point me to a good iterative search technique that would let me split that table out into 3 subsets:

Dogs
Fido
Spot
Spike

Cats
Sprinkles
Tom
Top Cat

Horses
Mr. Ed
Bossie
Flicka

I can get the first row with

Please Login or Register  to view this content.

And the match portion of that will tell me which row it was on. But I'm not sure how to convert that row number into a usable cell number so that subsequent searches know to start farther down. For example, the first horse is on row 6 of the search, cell Y9. So the next search shouldn't start until Y10. I've tried variations on the EVALUATE function, but with no luck.

Any ideas?

[NBVC]

Possibly something like:

Please Login or Register  to view this content.

this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around it.

Then copy down as far as you want.

You will need to change "Dog" to "Horse" and "Cat" for the other extracts.

Note: I assume the strings "Dog", "Horse", "Cat" are in range Y3:Y6000 of Data! sheet.

[James McMurray]

Thanks a million! That did the trick!