Excel 2016: Trouble with Match Function

[Cbird]

I have used the match function to populate the description labels for a series of data I am ranking which works well, until two of the values are exactly the same, then I get the same description for both values. Please reference the two yellow highlighted cells in the attachment.

[sandy666]

maybe try this

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Zer0Cool]

Not sure what you are trying to accomplish but I think part of the problem may be your concept of how INDEX and MATCH work (likely more so MATCH).

INDEX returns the value at an intersection of a row and column relative to a range you pass to INDEX. That is to say in a range like B2:G6, B2's index (row/column) would be 1,1. F5 would be 4,5. etc.

The second argument of INDEX is what row to look at. Using match in this fashion is generally done to find a unique entry in a column (going down the rows in a column) and return its relative row in the range. Note "unique" as match stops at the first match it find in a range.

The 3rd argument is the column to look in. Again match is typically used to find a unique entry across columns (like headers) to return which column INDEX uses. Again, match stopping at the first match it finds.

So your 3rd argument makes no sense.

Please Login or Register  to view this content.

This will always match the first cell in the range...B3 will always = B3 (itself).

Maybe if you describe what results you expect we can give a better answer for how to reach your goal.

[Cbird]

Thank you for trying to help me figure this out. I am indeed new to match and index. What I am trying to do is in Column F rank the values from Column D smallest to largest. Then in Column G identify the data label from Column B that is associated with each of the values in Column F. When I am done each value from Columns B and D should appear in Columns F and G, just in a new ranked order.

[JeteMc]

I believe the following array entered formula* will produce the order of 'Drivers' that you are expecting:

Please Login or Register  to view this content.

*1) Select cell G3,
2) Paste the formula into the formula bar,
3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Drag the fill handle down to cell G6.
Let us know if you have any questions.