On Sunday, December 2, 2012 2:17:03 PM UTC+2, plinius wrote:
Il 02/12/2012 12:43, ha scritto:

I have a list of unique reference numbers in column A

In column B I have the refence numbers from column A jumbled up and the same number can appear multiple times or not at all.

In column C I mark if the reference number in column A is a Bronze or Silver Class



In column D I now need the formula to do the following calculation.

Check if the reference number in column A appears anywhere in column B, if it does check if the reference number which it appears next to is a Bronze or Silver Class. If it is Bronze add 10 if it is Silver add 20.



The refernce number in column A will only appear once in column A but multiple times (or not at all) in column B



Can anyone help with this formula







=IF(ISERROR(FIND(A1,B1)),"",A1+(C1="silver")*20+(C 1="bronze")*10)



Hi,

E.

Thanks for the reply but this only finds it once. This is the table
A B C D
1456 1457 Bronze
1457 1456 Silver
1458 1456 Silver
1459 1457 Bronze
1460 1465 Bronze
1461 1456 Bronze
1462 1457 Silver
1463 1454 Silver
1464 1457 Silver
1465 1454 Silver
1466 1465 Bronze
1467 1464 Silver
1468 1457 Silver
As you can see in column D next to reference number 1456 I need to check wherever the number 1456 appears , then check if it is in a row which is Bronze or Silver and sum the value of either 10 or 20. I suspect this is an array formula. This formula then needs to be copied down the whole column D