Index match issue

Is your Excel 365? If so, please add this to your forum profile. Likewise if it's a Mac version, say so.

I have removed the duplicate post from your earlier thread - you may ask questions in ONE THREAD ONLY.

Your older thread needs marking as SOLVED - please go back and do this. Thanks.

Is it for Mac? If so, add this, too.

I think you need to add some notes to your sample workbook by way of signposting. It's all a bit confusing for the uninitiated!

Sorry, I am not following this fully.

There is a formula already in place. Are you saying that it does NOT do what you want?

If so, then replace formulae with a hard-coded expected result and, in your annotations, explain the LOGIC behind the result so that nobody has to try and reverse engineer your formula. I know it all makes perfect sense to you, but that's because it's your data!

Hello Ali,

Please tell me if it is better, I tried to put more information, but I dont know how to explain further - let me know if you have a "good example" sheet:

I am trying to reassemble information from different sheet into one, without using multiple pivot tables.
So I created one Pivot table where I will find the main Animal criteria (sheet "Work in progress" ; column C) , and I try to attach the other information with a formula INDEX MATCH.

The information I am seeking to attach to the "Work in progress" sheet, is to find for every "Animal criteria" (which can be found in both sheets "Work in progress" column C, and sheet "Animal analysis" ; column G) the different "classification of animal" (which is for now only in sheet "animal analysis", column O). Warning, their is multiple rows of the same Animal criteria, but with different Animal classification.

Therefore, in the sheet WORK IN PROGRESS, I wish that my columns:
-E shows "additional requirement" if it is one of the classification found in the sheet Animal criteria ;
-F shows "compliance +" if it is one of the classification found in the sheet Animal criteria ;
-G shows "compliance" if it is one of the classification found in the sheet Animal criteria.

I know it doesn't work the way I did it, because on the first Animal criteria, I know that it has only Compliance +, the second has the 3, and the last Additional requirement and compliance.

N.B. I left the initial formula in the sheet to help for the INDEX MATCH because the "Animal criteria" is very long and doesn't work with a "simple" index match or vlookup.

Thanks for your help!

OK. Not sure you have understood, but I asked:

1. That you add manually calculated expected results to your workbook INSTEAD of a non-working formula, and ...
2 ... that you add annotations to the workbook itself.

Sure - I can copy your notes into your workbook and work out what the correct answers should be, but I would prefer you to do it for the avoidance of confusion.

Sorry if you think I am being pernickety: I'm not. You are very close to your data, but we aren't.

That looks more like it - I shall take a proper look in a while.

Perhaps this will help.
I could not change the source of the original pivot table, so I put a new one on Sheet1.
1. On the Animal analysis sheet I added a helper column (E) which is populated using: =TRIM(LEFT(G6,SEARCH("1.1",G6)-3))
2. The pivot table on Sheet1 utilizes the helper column in both the Rows and Values area
3. The formula for Additional Requirement, Compliance + and Compliance is: =IF(COUNTIFS('Animal analysis'!$E$6:$E$55,$A4,'Animal analysis'!$O$6:$O$55,C$3),C$3,"")
Let us know if you have any questions.

Perhaps if it was illustrated, in a file, where the method in post #12 fails, then someone could come up with a better idea.