Hi all -
So I am trying to make a finder tool within a sales organization where a salesperson can pick from a list of products (dropdown using a data validation list) and once selected, current use cases appear below that. To set that up, I used an index formula that would pull from another data table. Info/photos:
Here, you see the dropdown and the selection "P33" with the matches below that:
The formula I used to pull up the matches and is in Cell B2 above is:
=IF(ISERROR(INDEX(ProductList!$A$1:$B$1996,SMALL(IF(ProductList!$B$1:$B$1996=$B$1,ROW(ProductList!$B$1:$B$1996)),ROW(1:1)),1)),"",INDEX(ProductList!$A$1:$B$1996,SMALL(IF(ProductList!$B$1:$B$1996=$B$1,ROW(ProductList!$B$1:$B$1996)),ROW(1:1)),1))
I managed that formula from an online entry found here:
How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group
The "ProductList" worksheet referred to in that formula is:
That is made on a separate worksheet. Basically, for each product, I listed the entity in additional entries so that I could list the products in their own cells.
My issue, as seen in the first photo, is that for the product "series" a broader search term, the finder is pulling up names multiple times as they have multiple products in that series.
Any thoughts on how I can code it to avoid duplicate entries in this search?
Glad to provide more info/screengrabs if needed.
Many thanks.
So I am trying to make a finder tool within a sales organization where a salesperson can pick from a list of products (dropdown using a data validation list) and once selected, current use cases appear below that. To set that up, I used an index formula that would pull from another data table. Info/photos:
Here, you see the dropdown and the selection "P33" with the matches below that:
The formula I used to pull up the matches and is in Cell B2 above is:
=IF(ISERROR(INDEX(ProductList!$A$1:$B$1996,SMALL(IF(ProductList!$B$1:$B$1996=$B$1,ROW(ProductList!$B$1:$B$1996)),ROW(1:1)),1)),"",INDEX(ProductList!$A$1:$B$1996,SMALL(IF(ProductList!$B$1:$B$1996=$B$1,ROW(ProductList!$B$1:$B$1996)),ROW(1:1)),1))
I managed that formula from an online entry found here:
How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group
The "ProductList" worksheet referred to in that formula is:
That is made on a separate worksheet. Basically, for each product, I listed the entity in additional entries so that I could list the products in their own cells.
My issue, as seen in the first photo, is that for the product "series" a broader search term, the finder is pulling up names multiple times as they have multiple products in that series.
Any thoughts on how I can code it to avoid duplicate entries in this search?
Glad to provide more info/screengrabs if needed.
Many thanks.