×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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

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

(OP)
Thanks, Skip (semi-retired wink). 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

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

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close