using Index, Match, Large, and Visible Cells in one formula

**jomili** · 09-16-2011, 02:12 PM

Okay, I think the title says it all. I have a formula which looks like this:

Please Login or Register  to view this content.

It returns a value from a cell two doors down from the largest number in column F. That part works fine.

However, I have an autofilter on my data. When I use the autofilter, I only want the formula to look at the visible cells. Is there a way to do this in the active formula, or does it require a VBA solution? I'd prefer to do have this be part of the formula if at all possible, so am looking for a solution.

Thanks in advance,
John

**NBVC** · 09-16-2011, 03:07 PM

Try something like:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER.

Note: Do not use whole column references with this formula... it may crash Excel or take very long to process.

**jomili** · 09-16-2011, 03:10 PM

Hmmm,

the data limitation is going to be a problem. I've got about 10K rows to check. Could/Should I set up dynamic ranges, and use those in the formula?

**NBVC** · 09-16-2011, 03:16 PM

Dynamic ranges should definitely help. 10,000 rows shouldn't be much of a problem, though.... I just tried the formula with 10,000 row ranges and it returned in a flash... but if you have multiple array formulas, it reduces the speed again...

**jomili** · 09-16-2011, 04:17 PM

I'm having trouble, so thought I would attach what I have to see what can be done. After I created my dynamic ranges I tried your formula, then got a circular result error. So, I went back to my original formulas. Using one of the two dynamic ranges ("Need") works, but the second, "Title", creates a circular reference, but I can't understand why. Instead of crying over it I thought maybe you could take a look. The formulas are in C1 and C2.

Thanks,
John

**NBVC** · 09-16-2011, 10:57 PM

Redefine Named ranges as:

Need:

Please Login or Register  to view this content.

Title:

Please Login or Register  to view this content.

Then apply array formula:

Please Login or Register  to view this content.

CSE confirmed

**jomili** · 09-19-2011, 09:34 AM

YES,YES,YES!!!

NBVC, you ROCK!

The formulas worked perfectly. Thank you so much for your help on this one!

**ROCKY4U** · 01-09-2013, 06:45 PM

Hi all,
I am trying to match two columns and return the matching values. One of the column is a filtered column. What formula should i write to only match the filtered/visible values in column 2 with the values in column 1. Below is formula i have right now and it also matches the hidden values which i dont want to..

=IF(ISERROR(MATCH($B1053,'Consolidate LDT'!$M$9:$M$413,0)),"na",$B1053)

**arlu1201** · 01-10-2013, 01:58 AM

Rocky4u,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.