retrieve multiple cells with identical cell next to it

[Perkett21]

Hey guys- first post.
Quick question- How do I use either Vlookup or some other command that with extract/index out all cell (numerical) values that are next to a certain cell.
Such as:
#boats| 5|
house| 2|
name| cp|
#boats|13|
#boats|3|

how would I get all #boats into a column easily?
thank you guys so much
-cp

[NBVC]

=IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$C$2,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:$A2))),"")

where table is in A2:B100, and C2 contains item to match in column A of the table...

Confirm this formula with CTRL+SHIFT+ENTER not just ENTER and copy down

[Perkett21]

This is kind of making sense but I am having trouble applying it to what I need.
What I need to do is extract all of the information from messy data and put it into a nice table.
In the example file I have here, I want to get out every timestamp (Column F) where "start" comes up in
the D column. and I want to do this for every single "Start".
After this I am going to do that for every value field in column D- but that is just replacing what I am looking for.
If you could look into a bit more, I would greatly appreciate it.
Thank you so much!

examplesearch.xlsx

[NBVC]

I used this formula in Sheet2:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

[Perkett21]

It is really close to working- It grabs the first Start time, but the remaining times are off. Would this be because of a row subtraction thing?
thanks again.
(what do you recommend is the best way to learn all of this stuff? being a college student I am just starting to scrape the top of deeper level excel)

[NBVC]

Sorry, that was a bit careless of me... I forgot to make a couple references absolute... try:

=IFERROR(INDEX(Sheet1!$F$1:$F$31,SMALL(IF(Sheet1!$D$1:$D$31="Start",ROW(Sheet1!$D$1:$D$31)-ROW(Sheet1!$D$1)+1),ROWS($A$2:$A2))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

what do you recommend is the best way to learn all of this stuff? being a college student I am just starting to scrape the top of deeper level excel

That's really a difficult question to answer. Most of what I (and many others here) have learned is from forums such as this one you and I are in. If you stick around and follow some threads, and try to answer a few, it is amazing what you will learn and grasp. Other than that, there are many websites from Excel gurus out there and books that teach some of the basics... but it depends on what you mean by "this stuff" really....

[Perkett21]

It worked! Thank you very much I appreciate it! I guess if I have any questions of the sorts I'll post it here. I still have to retrieve each of the other rows to the left but hopefully I will be able to figure it out.
Have a great day

[NBVC]

If you remove the $ from before the F's in the first part, then you can copy the formula to the right to grab adjacent column info... and if you put the formula somewhere in the middle of the sheet, you can drag left too...

Please Login or Register  to view this content.