Same formula but results are differ (Some expected result, some gave #Num! error)

No definite answers ... just some observations. The troubling formula in F3 and filled down is this:

=IF(LEN($G3),INDEX('D:\Users\fadilah.isnin\Documents\OOS Excel Report\Daily & Weekly OOS Report\Weekly OOS Report\To get Top 10\#NUM! error - ask around\[Data source.xlsx]Sheet1'!$E$1:$E$49991,SMALL(IF(('D:\Users\fadilah.isnin\Documents\OOS Excel Report\Daily & Weekly OOS Report\Weekly OOS Report\To get Top 10\#NUM! error - ask around\[Data source.xlsx]Sheet1'!$A$1:$A$49991=$C3)*('D:\Users\fadilah.isnin\Documents\OOS Excel Report\Daily & Weekly OOS Report\Weekly OOS Report\To get Top 10\#NUM! error - ask around\[Data source.xlsx]Sheet1'!$AT$1:$AT$49991=$G3),ROW($1:$636)),COUNTIF($G$3:G3,G3))),"")

Try changing that to:

=IF(LEN($G3),INDEX('D:\Users\fadilah.isnin\Documents\OOS Excel Report\Daily & Weekly OOS Report\Weekly OOS Report\To get Top 10\#NUM! error - ask around\[Data source.xlsx]Sheet1'!$E$1:$E$49991,SMALL(IF(('D:\Users\fadilah.isnin\Documents\OOS Excel Report\Daily & Weekly OOS Report\Weekly OOS Report\To get Top 10\#NUM! error - ask around\[Data source.xlsx]Sheet1'!$A$1:$A$49991=$C3)*('D:\Users\fadilah.isnin\Documents\OOS Excel Report\Daily & Weekly OOS Report\Weekly OOS Report\To get Top 10\#NUM! error - ask around\[Data source.xlsx]Sheet1'!$AT$1:$AT$49991=$G3),ROW($1:$49991)),COUNTIF


This will make the ranges and indexing number assignment boundaries agree.

Also the first argument to INDEX is $E$1:$E$49991. E1 is part of a merged cell. This could be causing some problems. I don't know. I've never tried that before. Try unmerging B1:E1.

Edit: disregard this paragraph. It is wrong.Also even with E1 not part of a merged cell the first two rows of $E$1:$E$49991 are headers. In F66 SMALL is receiving a k value (SMALL(array, k)) of 2 from the COUNTIF function. That would be the second row of $E$1:$E$49991 which is the header "Name".

Since we don't have access to your D:\ drive we can't test any of these changes ... but you can. Try making some of those changes and see what happens.

Hi there. Try this. I modified column AT in the source chart, to make all the rankings UNIQUE by assing a very small, and variable, number to each value. I then modified/tidied up the formulae in F and G of ther main sheet. All values are now shown and there are NO duplicate names.

OK?

In row 1, the term +row()/10^6 adds 1/1,000,000 to the calculated value. So a value of 4.2 becomes 4.200001.

In row 10, the term +row()/10^6 adds 10/1,000,000 to the calculated value. So a value of 4.2 becomes 4.200010.

The additions are so small they make no practical difference to their value... but they are now no longer the same. This "breaks" ties (albeit by an unselective parameter - the row number) ands allows values to be ranked and allows LOOKUPS to return the correct values.