MrSamExcel
Board Regular
- Joined
- Apr 6, 2016
- Messages
- 50
- Office Version
- 365
- 2021
- Platform
- Windows
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
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