Look up across all columns with name containing specific text

[MrSamExcel]

What formula can determine if each Name from 'result' tab has has any *Duration* value from 'source' tab? Result in 'result' tab columnB can be the Duration value itself, or can tell me if blank/not-blank, etc; I'll use this as part of an =IF() statement [in words, something like "If Name has a Duration, then return "Yes" in this cell"]. As you can see from my example, there are multiple field names that contain Duration -- such as "/PortfolioComp/AvgDuration", "/PortfolioComp/LeverageDuration" -- and I need to know if at least one of those fields contains a non-blank value.

If there was only one field in 'source' tab with Duration in the field I would use:
=INDEX(source!$1:$1048576,MATCH($A2,INDEX(source!$1:$1048576,,MATCH("/PortfolioComp/Fund",source!$2:$2,0)),0),MATCH("/PortfolioComp/AvgDuration",source!$2:$2,0))
And I know I could use nested OR() repeating the formula above and changing the second MATCH value for each Duration field, but cumbersome and breaks if 'source' tab field names change down the road. Is there a name contains "*Duration*" function or another more efficient way of accomplishing the desired results?

Restrictions:
1. Cannot modify the 'source' tab data in any way
2. No VBA
3. Ideally no add'l helper columns or array formulas, especially if the idea above could work without the use of these.

Excel 2016 64-Bit

sheet name: result

sheet name: source

 

[jasonb75]

You will not be able to do what you want with all of your 'restrictions' applied, something will have to give.

Best guess without a testable sample (i.e. posted using the XL2BB add in so that it can be copied to excel).

=IF(SUMPRODUCT((INDEX(Source!$A$3:$H$7,0,MATCH("/PortfolioComp/Fund",Source!$A$2:$H$2,0))=$A2)*(RIGHT(Source!$A$2:$H$2,8)="Duration"),Source!$A$3:$H$7),"Y","N")

Which is an array formula, although it may not need to be array confirmed.

 

[MrSamExcel]

You will not be able to do what you want with all of your 'restrictions' applied, something will have to give.

Best guess without a testable sample (i.e. posted using the XL2BB add in so that it can be copied to excel).

=IF(SUMPRODUCT((INDEX(Source!$A$3:$H$7,0,MATCH("/PortfolioComp/Fund",Source!$A$2:$H$2,0))=$A2)*(RIGHT(Source!$A$2:$H$2,8)="Duration"),Source!$A$3:$H$7),"Y","N")

Which is an array formula, although it may not need to be array confirmed.

This is great, thank you!
1) You are correct, I should have specified the restriction as "array confirmed (CTRL-SHIFT-ENTER)"
2) The formula you provided works as long as "Duration" is the last 8 characters of the field name: (RIGHT(Source!$A$2:$H$2,8)="Duration"). This is a reasonable risk to assume in my situation, but curious if there is a way to reference fields that have "Duration" anywhere in the name, such as "AvgDuration2"?

 

[jasonb75]

Curious as to why you would want to restrict a CSE array, but not an array function? CSE arrays can be preferable to non array alternatives and in many formulas you can circumvent the CSE confirmation with a little creativity, possibly reducing efficiency at the same time.

You could use ISNUMBER(SEARCH("Duration",Source!$A$2:$H$2)) as an alternative to RIGHT(....), I don't think that it would be any less efficient.

 

[MrSamExcel]

YES, exactly what I was hoping for, thanks again!
I have to transfer this spreadsheet to individuals who are less adept at Excel. I'd like to enable them to make small modifications to the setup as their data sets expand, but CSE arrays are typically an obstacle for this audience. What you provided is simpler than the non-array formula I referenced originally and will be easy enough to transition to others.