Formula finding data in one cell but not another

%3CLINGO-SUB%20id%3D%22lingo-sub-2166079%22%20slang%3D%22en-US%22%3EFormula%20finding%20data%20in%20one%20cell%20but%20not%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2166079%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20difficulty%20trying%20something%20new%20in%20my%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20the%20spreadsheet%20works%20up%20to%20the%20point%20of%20this%20formula%20%3A%3C%2FP%3E%3CP%3EA%20list%20of%20plants%20is%20entered%20into%20a%20data%20tab%2C%20there%20are%20then%20individual%20tabs%20with%20the%20different%20category's%20of%20plants.%3C%2FP%3E%3CP%3EOn%20each%20tab%20for%20each%20category%20I%20have%20written%20the%20name%20of%20the%20category%20so%20I%20can%20index%20this%20from%20the%20data%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20using%20column%20D%20from%20the%20data%20tab%20to%20match%20AA1%20on%20this%20separate%20tab%20and%20this%20was%20then%20picking%20up%20the%20information%20from%20cell%20%3A%20Data%20AA2%20-%20AA10000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20now%20trying%20to%20change%20this%20so%20where%20I%20said%20earlier%20I%20have%20written%20the%20name%20of%20the%20category%20to%20index%20with%2C%20I%20have%20now%20changed%20this%20to%20another%20word%20that%20is%20not%20originally%20entered%20when%20the%20data%20in%20the%20data%20tab%20is%20pasted%20in.%20To%20do%20this%20I%20have%20used%20a%20formula%20to%20look%20in%20cell%20D%20(where%20data%20originally%20came%20from)%20and%20change%20this%20name%20slightly.%20I%20have%20also%20changed%20and%20matched%20this%20name%20on%20the%20separate%20tab%20for%20the%20index%20lookup.%20This%20new%20data%20is%20now%20in%20column%20Q.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20formulas%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20(working)%20-%3DIFERROR(INDEX(Data!%24AA%242%3A%24AA%2410000%2CSMALL(IF(Data!%24%3CFONT%20color%3D%22%23FF6600%22%3ED%3C%2FFONT%3E%242%3A%24%3CFONT%20color%3D%22%23FF6600%22%3ED%3C%2FFONT%3E%2410000%3DAA%241%2CROW(Data!%24%3CFONT%20color%3D%22%23FF9900%22%3ED%3C%2FFONT%3E%242%3A%24%3CFONT%20color%3D%22%23FF9900%22%3ED%3C%2FFONT%3E%2410000)-MIN(ROW(Data!%24%3CFONT%20color%3D%22%23FF9900%22%3ED%3C%2FFONT%3E%242%3A%24%3CFONT%20color%3D%22%23FF9900%22%3ED%3C%2FFONT%3E%2410000))%2B1)%2CROW()-2))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20(not%20working)%20-%3DIFERROR(INDEX(Data!%24AA%242%3A%24AA%2410000%2CSMALL(IF(Data!%24%3CFONT%20color%3D%22%23FF0000%22%3EQ%3C%2FFONT%3E%242%3A%24%3CFONT%20color%3D%22%23FF0000%22%3EQ%3C%2FFONT%3E%2410000%3DAA%241%2CROW(Data!%24%3CFONT%20color%3D%22%23FF0000%22%3EQ%3C%2FFONT%3E%242%3A%24%3CFONT%20color%3D%22%23FF0000%22%3EQ%3C%2FFONT%3E%2410000)-MIN(ROW(Data!%24%3CFONT%20color%3D%22%23FF0000%22%3EQ%3C%2FFONT%3E%242%3A%24%3CFONT%20color%3D%22%23FF0000%22%3EQ%3C%2FFONT%3E%2410000))%2B1)%2CROW()-2))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20now%20not%20returning%20the%20information%20from%20Data%20tab%20AA2%3AAA10000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel%20formula.png%22%20style%3D%22width%3A%201615px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257883i5726003FAE2ECD5C%2Fimage-dimensions%2F1615x281%3Fv%3D1.0%22%20width%3D%221615%22%20height%3D%22281%22%20role%3D%22button%22%20title%3D%22excel%20formula.png%22%20alt%3D%22excel%20formula.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2166079%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2166142%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20finding%20data%20in%20one%20cell%20but%20not%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2166142%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F978798%22%20target%3D%22_blank%22%3E%40Alessiothegreat%3C%2FA%3E%26nbsp%3BBefore%20diving%20into%20the%20formula%2C%20may%20I%20ask%20why%20you%20are%20creating%20separate%20sheet%20for%20each%20category%20of%20plant%20in%20the%20first%20place.%20You%20know%20you%20can%20filter%20the%20main%20data%20table%20on%20demand%2C%20without%20having%20to%20create%20separate%20sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

Thanksexcel formula.png

7 Replies

@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.

@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

@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.

@Riny_van_Eekelen 

As i have created this for my boss and he is not very good on computers so have created this spreadsheet so all he has to do is copy and paste. Also it is alot quicker for the formulas to automatically sort all the data exactly how i want it. 

@Alessiothegreat Okay! Difficult to diagnose a formula that doesn't seem to work without having the file/data to test on.

@Riny_van_Eekelen 

Thankyou for your help i really appreciate it.

 

Please see attached the file.

 

I am trying to for instance on the "Shrub Std" tab to have columns AB-AF etc work the same as the "conifer" tab does but using the match of Data!Q or Data!P instead of cloumn D. 

 

@Alessiothegreat Am having great difficulty working with your workbook. It has over 465 thousand formulae and, therefore, it's near impossible to work with it (for me at least). My feeling is that you are overly complicating things and am surprised that your boss (who is not very good with computers) is able to work with this workbook. I'm not. Sorry.