Excel find values in multiple rows and return the cell value

0
votes

I have a excel document that contains "column names" from b9:b59 and then I have an "options" column that is h9:h59 those columns can contain different values.

I'm trying to return all the "column names" where the "options" contains the words "constraint"

So if h10 = constraint then I want the corresponding "column name" from b10. If h10 and h11 and h12 contains constraint then I want corresponding column names from b10,b11,b12.

I've looked at index, match & vlookup. But I'm not getting it. Any help is appreciated.

Here is my excel spread sheet. In the example below I want to be able to find all the "Column Name" values that contain the "Options" constraint". So I am looking for test2,test3,test4

I need to be able to grab the value from the rows returned and append text to it test2 asc, test3 asc, test4 asc

enter image description here

2
excel
You want all these returned to one cell? – Scott Craner
@ScottCraner I guess 1 cell. I will need to modify them (like append text to them) – webdad3
Can you create a simple example in Excel and add a screen capture of it to your question? It's a little hard to visualize what your goal is. – devuxer
This sounds like a job for a filter... no formula or VBA required – tigeravatar
tigeravatar - Obviously I'm not an excel guy... Can you provide a link to an example of how to use a filter? – webdad3

2 Answers

1
votes

I would use an IF statement.

I always nest my IF statements within an IFERROR statement.

=IFERROR(IF(H10="Constraint",B10,""),"")
0
votes

Select the column headers

Go to data tab

hit filter button

Job = done