Named Range that has Gaps (or at least a gap) - Not working for INDEX and MATCH functions 2

[kjv1611]

I've already used this for other formulas/functions with no issue, but the INDEX and MATCH issue is bugging me. Either it's just not possible or I need to do it somehow differently.

The setup is for an Excel workbook where the data range is split over two sections. The reason for split is due to needing some rows at top of first 2 pages, but not on third for printing. The split ranges only came into the scene when it was desired to have 2 pages of data input for the process. Before that, it was one page of data, so no big deal for named ranges.

Now I have named ranges such as: MyWorksheet!$G$12:$G$53,MyWorksheet!$G$61:$G:$102

Most formulas and VBA doesn't seem to care that the range is split, but INDEX and MATCH sure do. MATCH gives "N/A" and INDEX gives "#VALUE!".

If the arrays (ranges) are not split over 2 sub-ranges, then they work fine.

I may just punt and build a VBA solution but trying to skip it for this type setup.

Thanks for any thoughts. Will come back to this in an hour or so.

Sorry not much detail - on the run for other matters. Can likely get back after lunch with more detail.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

Just specify one range spanning the rows, MyWorksheet!$G$12:$G$102. Won't matter, cuz you'll be using a lookup and it would be unlikely that column G would contain any valid lookup values in rows 54:60.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

The second list of arguments of INDEX function has four arguments: range, row, column, area number. So =INDEX(MyWorksheet!$G$12:$G$53,MyWorksheet!$G$61:$G$102,1,1,1) returns contents of G12, or G61 if the last argument is 2.
BTW, AREAS function returns number of subranges (areas) in range.

Edit:
The INDEX function returns reference to range and is very flexible. Used without indicating row and column returns reference to the whole area. For instance:
=INDEX(MyNonContiguousRange,,,2) returns reference to second area and can be an argument in other functions: =SUM(INDEX(MyNonContiguousRange,,,2)),
=ROWS(INDEX(MyNonContiguousRange,,,2)) returns number of rows in the second area,
=ROW(INDEX(MyNonContiguousRange,,,2)) returns number of row of the first cell in the second area.

combo

 

[kjv1611]

Thanks, Skip (semi-retired ). I was thinking the same thing as I left the building yesterday. I may just do that to simplify if it works. I am just verifying it won't cause issues related to the named ranges. If I have to, I can just create a secondary named range of course. But I'm looking into what combo says first.

combo, I'm going to re-read and re-read some more to make sure I'm getting what you're saying.

Will post back hopefully today with whatever I end up doing.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[combo]

Depending on your screen resolution, the axamples I posted may be hard to read. They are three separate ones: (1) =INDEX(MyNonContiguousRange... with summing up the area, (2) =ROWS(INDEX(MyNonContiguousRange... - number of rows in area, (3) =ROW(INDEX(MyNonContiguousRange... first row of area .

combo

 

[kjv1611]

As an update, I THINK the problem was just the MATCH function, not INDEX. It appears I can use MATCH on one continuous range - so do what Skip suggested of including the cells between (should be no problem, as that named range is not used anywhere else to date). Then INDEX can refer to the non-continuous ranges no problem.

So my final formula piece will look something like:
=INDEX(NonContinuousRange,MATCH(SearchString,ContiguousRange,0),1)

I'm piecing that into the formula that finds what #s to compare. Fun stuff. At times, it seems it would be easier still to just toss the whole thing to VBA so I can split everything out. However, I thought for performance sake, INDEX(MATCH()) inside the basic calculation formula would be better.

I'll post back if anything changes in this, but at this point, the whole INDEX(MATCH()) issue seems resolved so I can move on to piecing everything together.

Thanks a ton for helping me think through it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57