Forum Discussion

Alessiothegreat's avatar
Alessiothegreat
Copper Contributor
Feb 25, 2021

Formula finding data in one cell but not another

Hi everyone,

 

I am having difficulty trying something new in my formula.

 

How the spreadsheet works up to the point of this formula :

A list of plants is entered into a data tab, there are then individual tabs with the different category's of plants.

On each tab for each category I have written the name of the category so I can index this from the data tab.

 

I was using column D from the data tab to match AA1 on this separate tab and this was then picking up the information from cell : Data AA2 - AA10000.

 

I am now trying to change this so where I said earlier I have written the name of the category to index with, I have now changed this to another word that is not originally entered when the data in the data tab is pasted in. To do this I have used a formula to look in cell D (where data originally came from) and change this name slightly. I have also changed and matched this name on the separate tab for the index lookup. This new data is now in column Q.

 

Please see the formulas below.

 

 

Current (working) -=IFERROR(INDEX(Data!$AA$2:$AA$10000,SMALL(IF(Data!$D$2:$D$10000=AA$1,ROW(Data!$D$2:$D$10000)-MIN(ROW(Data!$D$2:$D$10000))+1),ROW()-2)),"")

 

What I am trying (not working) -=IFERROR(INDEX(Data!$AA$2:$AA$10000,SMALL(IF(Data!$Q$2:$Q$10000=AA$1,ROW(Data!$Q$2:$Q$10000)-MIN(ROW(Data!$Q$2:$Q$10000))+1),ROW()-2)),"")

 

This is now not returning the information from Data tab AA2:AA10000.

 

Help would be greatly appreciated.

 

Thanks

Formulas and Functions

7 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 25, 2021

    Alessiothegreat Before diving into the formula, may I ask why you are creating separate sheet for each category of plant in the first place. You know you can filter the main data table on demand, without having to create separate sheets.

    • Alessiothegreat's avatar
      Alessiothegreat
      Copper Contributor
      Feb 25, 2021

      Riny_van_Eekelen 

      This is to create an availability list of stock we currently have which regularly gets emailed to clients for them to choose what they want and see what we have in stock. It is much easier for these to be categorised into different tabs.

       

      Thanks

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Feb 25, 2021

        Alessiothegreat Why then not just filter and copy paste into a new sheets and then delete the master table befor emailing?

        Otherwise, if you are a MS365 subscriber, the FILTER function could do this also.