View original (French)

Puzzle: Index+Equivalent+Duplicates

Solved
Melycea -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello everyone,

I am using Excel 2007.
I have a workbookA where cell D2 of sheet 1 is automatically filled with a corresponding value from column C of workbookB. This filling occurs under the condition that:
- cell B2 of sheet1 in workbookA matches column B of sheet2 in the same workbook translation [workbookA]sheet1!B2 = [workbookB]sheet2!B:B
- the corresponding cell in sheet2 of workbookA matches column A of workbookB
- cell C2 of sheet1 in workbookA matches column B of workbookB
translation [workbookA]sheet1!C2 = [workbookB]sheet1!B:B

For all this, I used the formula: 
{=IFERROR(INDEX([workbookB]sheet1!C:C;MATCH(INDEX(sheet2!C:C;MATCH(sheet1!B2;sheet2!B:B;0))&C2;[WorkbookB]sheet1!D:D;0))&"";"")}
This formula works very well except in cases where I have a duplicate in column [workbookA]sheet1!B:B, as all rows with the same value are filled as well, ignoring column [workbookA]sheet1!C:C, and I can no longer modify this value.

Why?! How can I fix this?!

I know that the problem is not obvious from the outside, so I am sending you a structure of my workbooks that highlights my problem.
Workbook A: http://cjoint.com/?ADCpKpXQdp5
Workbook B: http://cjoint.com/?ADCpLdhOnSo

Thank you very much to those who have the courage to look into this.

Configuration: Windows XP / Internet Explorer 7.0

5 answers

Answer 1 / 5
gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
 
Hello

When you use EQUIV to search for a value in a column, it is necessarily the first corresponding value that is always found.

If you have duplicates, and you want to find the next value, you then need to use the DECALER function to position yourself, but that will complicate your formula.
You might need to have another column to manage this or work with the index of column A if it is usable.
--
Always zen
1