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!

Register Yourself Login