# INDEX/MATCH Lookup for only visible text cells

• Hi Guys,

So i'm trying to return data from a table using this array formula - the Formula works like a charm but I need it to pickup when the data table has been filtered. I've been trying to use SUBTOTAL but I cannot get it it return text values, only numbers.

Here is my formula:

{=IFERROR(INDEX('ICE Incoming'!\$P\$2:\$P\$5000,SMALL(IF('Supplier Performance'!\$AG\$5='ICE Incoming'!\$B\$2:\$B\$5000,ROW('ICE Incoming'!\$B\$2:\$B\$5000)-MIN(ROW('ICE Incoming'!\$B\$2:\$B\$5000))+1,""),ROW(A1))),"")}

Column P is what I want returning, column B has the criteria values in, AG5 is the criteria.

Hope that makes sense.

• Re: INDEX/MATCH Lookup for only visible text cells

Try this array* formula:[COLOR="#0000FF"]

=IFERROR(INDEX('ICE Incoming'!\$P\$2:\$P\$5000,SMALL(IF(((SUBTOTAL(3,OFFSET('ICE Incoming'!\$B\$2:\$B\$5000,ROW('ICE Incoming'!\$B\$2:\$B\$5000)-MIN(ROW('ICE Incoming'!\$B\$2:\$B\$5000)),,1))>0)*('Supplier Performance'!\$AG\$5='ICE Incoming'!\$B\$2:\$B\$5000)),ROW('ICE Incoming'!\$B\$2:\$B\$5000)-MIN(ROW('ICE Incoming'!\$B\$2:\$B\$5000))+1),ROW(A1))),"")[/COLOR]

[ARF]x[/ARF]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: INDEX/MATCH Lookup for only visible text cells

Quote from NBVC;785391

Try this array* formula:

=IFERROR(INDEX('ICE Incoming'!\$P\$2:\$P\$5000,SMALL(IF(((SUBTOTAL(3,OFFSET('ICE Incoming'!\$B\$2:\$B\$5000,ROW('ICE Incoming'!\$B\$2:\$B\$5000)-MIN(ROW('ICE Incoming'!\$B\$2:\$B\$5000)),,1))>0)*('Supplier Performance'!\$AG\$5='ICE Incoming'!\$B\$2:\$B\$5000)),ROW('ICE Incoming'!\$B\$2:\$B\$5000)-MIN(ROW('ICE Incoming'!\$B\$2:\$B\$5000))+1),ROW(A1))),"")

[ARF]x[/ARF]

Thank you very much - works like a charm

• Hello,

I'm having similar issues and was able to take advantage of your reply. I just need to go 1 step further.

I have the following index/match lookup only in visible cell working properly, however....

=IFERROR(INDEX(\$F\$2:\$F\$500,SMALL(IF(((SUBTOTAL(3,OFFSET(\$G\$2:\$G\$500,ROW(\$G\$2:\$G\$500)-MIN(ROW(\$G\$2:\$G\$500)),,1))>0)*(\$K\$517=\$G\$2:\$G\$500)),ROW(\$G\$2:\$G\$500)-MIN(ROW(\$G\$2:\$G\$500))+1),1)),"")

Column F is what I want returning, Column G has the criteria value in them. K517 is the lookup value.

Now from Column F there are more then 1 results to be return. I want to use the "&" and string all the return results in a single cell. How should I approach?

To clarify, Say I want to look up "Adam" (K517) it returned the first value "red" from Column F. However, there are also "yellow and Blue" that matches "Adam". So I want in a cell to display (Red, yellow, blue) based on the filtered cell. Hope that make sense, thank you in advance.

James,

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!