Look up across all columns with name containing specific text

MrSamExcel

New Member
Joined
Apr 6, 2016
Messages
35
Office Version
  1. 2016
Platform
  1. 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
1591475439992.png

sheet name: result

1591475403628.png

sheet name: source
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,448
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 6, 2016
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,448
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 6, 2016
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top