Returning a column within a Named Range or Structured Table array of a given value

Calgary_Neil

Board Regular
Joined
Apr 5, 2014
Messages
79
Hello, thanks for looking

I was hopping that my last post ( Structured Table Names for current row ) would have given me workable answer to this problem.
I need to find in array (7*20+ x 25 rows) if a number or greater is a selected in a row. This done by checking the 1stcolumn in a set of 7 for that value, for that row i.e.:
N( MOD( COLUMN( $B82:IV82) +4, 7) =0) and N( $B82:$IV82 >= 4)

Now the 7th cell check does not need to a “THE” row, just an array of the same length, keyed properly. When I replace the check value array with Index(Match)) it #Ref out. So that’s an array problem(??).

So what I would like is the cell reference(?)** of the first value in the proper column & row meeting the check Value & ID given. I would prefer that it uses named ranges or structured tables as I’m trying to make this small-tight-flexible-understandable. {Ha Ha, dream on}.

Foggy in the North, Neil
Excel 365 32 bit

**I can work this out, but need the Subset number ultimately. i.e. QUOTIENT( COLUMN()+adjustment, 7)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
End solution:
=QUOTIENT( MATCH( 1,
N( MOD( COLUMN( INDEX( Prod_Tech,1,0)) +4, 7) =0) *
N( INDEX(Prod_Tech, MATCH( AW22, Prod_TechID, 0), 0)>=AX22 ),
0) +5, 7)
where
Prod_Tech is the array,
Prod_TechID is the first column of the array, (the ID column)
AW22 is the ID of which row in the array to work on,
AX22 is the first value to find on that array row in a keyed column.

Any better ways out there?
Neil, in the snowy north
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top