MATCH function workaround - nesting ADDRESS function?

Hi clever people,

I was feeling all very smug with myself until I came across this problem!

I have a sheet (attached to this post) which essentially gathers the scores of a psychological assessment and then returns some indications on what the scores may mean.

There are 15 component scores and these are labelled A to O. The scores are entered manually and they range from 1-99%.

A part of the analysis process was to look at the top and bottom 5 scores and get the paragraphs of text that give an indication of what the score means. In this example the paragraphs of text are replaced by numbers 1 to 45 and are stored in the 'Data Interpretations' table at the bottom of the sheet.

In terms of identifying the top and bottom 5 scores I simply used the LARGE and SMALL functions which was fine, even if there were duplicate scores, this worked fine.

What I also needed was the equivalent component so I know which score this represented, and hence also get the correct interpretation text.

So, in the example spreadsheet in the Top 5, there are two components that have the same score of 95% - Components G and H. In the Bottom 5 there are again two components with the same score of 4% - Components C and N.

So, I used the follow equation to identify the component associated with the % score. (In this example we are looking at the Bottom 5 and rank number 4)

(INDIRECT(ADDRESS((MATCH((SMALL($D$9:$D$23,G21)),$D$9:$D$23,0))+8,3))
This finds the location of the corresponding component to the % score in the rank 1 to 5, depending on what ranking number is in row G (1 to 5), and then returns the address for that cell. Using INDIRECT I get the contents of the cell.

This is all good and fine, but I realised I had a problem when with duplicate scores, which I understand is a problem with the MATCH function, only finding the first occurrence. This led to the same components appearing twice in the list if there are duplicate scores. So in the first instance I created an IF equation which looks like this:

=IF((INDIRECT(ADDRESS((MATCH((SMALL($D$9:$D$23,G21)),$D$9:$D$23,0))+8,3)))=$H20,"Duplicate",(INDIRECT(ADDRESS((MATCH((SMALL($D$9:$D$23,G21)),$D$9:$D$23,0))+8,3))))

This effectively says, If the component returned is the same as the the one above, return "Duplicate", otherwise just return the answer - which works fine. I figured that you will only get a duplicate with the previous score, so by checking the previous cell you can be sure of knowing either way.

Now this is where I have hit a roadblock.

I figured that I could replace the "Duplicate" with a new INDREICT/ADDRESS/MATCH function that only searched for the next occurrence of the score in the range that came after the first occurrence. Eg. Cell + 1. So by passing the limitations of the MATCH function and just moving the search lookup array location to the cell after the first occurrence of the score through to the last cell as before.

So, for the Rank number 4 in the bottom, 5, this below function returns cell $D$12, which is the cell after the first occurrence of the 4% score. To achieve this I simply changed the +8 to +9 and 3 to 4 at the end of the equation.
=(ADDRESS(MATCH(SMALL($D$9:$D$23,4),$D$9:$D$23,0)+9,4))
I see this as identifying the start of the new range.

So, in the below equation, what I want to do is replace the $D$9 (which is the start of the whole range), with the (ADDRESS(MATCH(SMALL($D$9:$D$23,4),$D$9:$D$23,0)+9,3)), thus starting the range after the first occurrence.

(INDIRECT(ADDRESS((MATCH((SMALL($D$9:$D$23,G21)), $D$9 :$D$23,0))+8,3)))

It would 'ideally' look like:
IF((INDIRECT(ADDRESS((MATCH((SMALL($D$9:$D$23,G21)),$D$9:$D$23,0))+8,3)))=$H20,(INDIRECT(ADDRESS((MATCH((SMALL($D$9:$D$23,G21)),(ADDRESS(MATCH(SMALL($D$9:$D$23,4),$D$9:$D$23,0)+9,4)):$D$23,0))+8,3))),(INDIRECT(ADDRESS((MATCH((SMALL($D$9:$D$23,G21)),$D$9:$D$23,0))+8,3))))

But I get an error - Not sure where to go with this and my eyes are going square looking at it. Can anyone please help?

Many thanks,

Mark

You are getting the NAs because you change the output in your Component to Duplicate - therefore when the other cells reference this Duplicate it can't find it in the row and returns NA.

I've attached a fix for you. Basically you need a column with unique scores which is accomplished by =D9+row()/100000. Then you just change your component look up equation to focus on this area of unique scores.

Hope this helps.

That all seems to work nicely - a lot more elegant. Thank you so much for your help.

To close this thread off and for anyone's future reference, with your fix, I removed the entire IF statement and was left with:

=(INDIRECT(ADDRESS((MATCH((SMALL($E$9:$E$23,G22)),$E$9:$E$23,0))+8,3)))

Many thanks,

Mark