CODEWITHSUNDEEP
excelif-statementfilterexcel-formulaexcel-2010
user2991160
user2991160

Reputation: 19

Filter option in a if formula

I have searched every where, but I cannot find an answer to this.

I have a sheet containing information regarding different pipes running under a railway. There are different owners of these pipes (18 to be exact) through out the length of the railway.

Here is an example

ID    TYPE    INFO1    INFO2    OWNER

 1     A       GAS     STAYS     GC

 2     A       TELE    GOES      TC

 3     B       GAS     GOES      GC

 4     A       POW     STAYS     EC

 ...

and so on so forth for 500 pipes.

If I then use a filter option to only show all pipes from one owner, I would like the owners contact information to be inserted in cells B3:F6. I have the contact information in another sheet.

Is this possible? If yes, then how do I do it?

Upvotes: 0

Views: 78

Answers (1)

barry houdini
barry houdini

Reputation: 46361

Try putting in a helper column with a formula like this:

=SUBTOTAL(3,A2)

[Assuming column A is a column that will be populated for every row]

That will show a 1 in visible rows, so you can get the first visible owner with a formula like

=INDEX(Owner,MATCH(1,Filter,0))

....and you can use that in lookup/index formulas to retrieve contact info for that owner

Upvotes: 1

Related Questions