Returning Multiple Results into different columns from a master list

Hi everyone,

I'm new here and this is my first question so if it is structure strangely just let me know and I can try and explain the problem I am having a little bit more clearly.

So my Problem:

I have attached a workbook that I hope illustrates my situation.

I am looking to be able to search a list for a mark of relevancy (In this case yes) and return the value in the cell next to it (Document Name in this case).

I have been trying to do this by using this formula Type_of_Name_1_Relevant

=IF(ISERROR(INDEX(Type_of_Name_1_Table,SMALL(IF(Type_of_Name_1_Relevant=$B$12,ROW(Type_of_Name_1_Relevant)),ROW(1:1)),7)),"",INDEX(Type_of_Name_1_Table,SMALL(IF(Type_of_Name_1_Relevant=$B$12,ROW(Type_of_Name_1_Relevant)-1),ROW(1:1)),7))
(B12 just contains the word yes)

This formula works well for the first column that I have, but it does not work when it comes to any column after that.


Possible solutions that I have thought of

If I could find a way to modify my formula to match multiple criteria as opposed to just the one that it checks now then I could populate the Names column and then return values if The name and a yes appears

Other than that I am pretty new to excel in general and have been able to do what I have by perusing others solutions to answers so any expertise that can be offered will be welcomed.

As I said at the start I hope my explanation of the problem is enough, and if it is not I will do what I can to change that.

I don't fully understand how it works, I found it on a website and got it working with some tweaks, as I mentioned I'm pretty new to excel was just playing around with others formulas for the most part.

What I'm using it for is to search a column of the worksheet and find the word "yes"(located in cell B12). Once it has found the word it fills in the cell in the next sheet with the document name (Returned Result in my example)
However,
The problem is that I need the results separated into columns on the next sheet, and the current formula for whatever reason does not work after the first column.

So realistically the formula I am using is useless to me at this point, because it fails to meet one of my basic needs.

So my question is, Can I search a column for a string of text and return a value in a different cell, and can I easily populate the data into columns on another sheet.

I can very easily be completely unattached from that formula haha.

Any help you can offer will be greatly appreciated.

dredwolf is god!

This is awesome, I don't understand at all what it is doing and it does the job, however I seem to be having a problem with it and I hope its an easy fix.

When I get up to G1 in the formula

Formula:

Please Login or Register  to view this content.

the formula stops returning results.

Maybe I am just pushing the limits of the formula or maybe I am using it the wrong way.

Thanks for your help this looks great, and I really hope that I can get it working because this will save me so much work

SpnMan

I have a named range for the whole table. It is substantially bigger than my example (A1:H6934)

Is my table too large?

Do I need to maintain my named ranges on the different sections? Does that play into recognizing the different columns.

I cannot upload right now, at work, but I will definately extend my example to see if it works, and then upload the result.

Thanks again for all your help, I am definately much closer to the end result than I was before you saw it in your heart to help.

SpoonMan22

Ok, I think I am starting to get the idea.

So each section has a named range (and the section relates to the columns on the other sheet), So in your workbook both the relevant column and the sections have named range, are both of these necessary?

I don't understand how indirect and substitute work so getting a grasp of your formula is a little rough, but indirect finds the yes, and then substitute returns the document name?

I am still having trouble making it larger though, do the names of the ranges matter?

Dont apologize for anything, you know exactly what you are doing I'm the one making this difficult .

So to clarify, I need the named ranges to cover the "Relevant" column (F something to F something else) and the names to be the same as the column headers on sheet 2 with the addition of the named range ending in relevant.

As I have mentioned previously you are a god, I cannot even begin to thank you. Just dont be suprised when the church of dredwolf starts up haha