Finding Match in actual position.

[chandan12]

When we use Match function it returns the relative position of an item. And if we have same items in a data sheet the result mentioned first cell position repeatedly. My problem is,

"Is it possible to get exact position of different cells?" For example, when 200 is mentioned in F2,F4,F5,F6 and F7 and we match 200 of E3 with this function [=Match(F2,E:E,0), the result returns 3, #N/A,3,3,3,3. But I want to get matching result in actual position like, 2,4,5,6,7.

[Glenn Kennedy]

In G2, paste this array formula.:

=IFERROR(IF($D2="","",SMALL(IF($D2=$E$2:$E$90,ROW($E$2:$E$90)-ROW($D$2)+1),COLUMNS($A:A))),"")

Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...n Drag across ( to at least column P) and then down.

[Tony Valko]

What version of Excel are you using?

I see your file is in the *.xls format. So, Glenn's formula will not work in that file format.

[Glenn Kennedy]

Indeed!! Thanks Tony.
If you are using 2003, you'll have to use this 'un:

=IF(ISERROR(IF($D2="","",SMALL(IF($D2=$E$2:$E$90,ROW($E$2:$E$90)-ROW($D$2)+1),COLUMNS($A:A)))),"",IF($D2="","",SMALL(IF($D2=$E$2:$E$90,ROW($E$2:$E$90)-ROW($D$2)+1),COLUMNS($A:A))))

also array-entered.

[ChemistB]

Modifying Glenn's formula for Excel 2003

=IF(COUNTIF($E$2:$E$90, $D2)>= ROWS($A$1:$A1), ERROR(IF($D2="","",SMALL(IF($D2=$E$2:$E$90,ROW($E$2:$E$90)-ROW($D$2)+1),COLUMNS($A:A))),"")
Still is an ARRAY formula so use cNTRL SHFT ENTER

[chandan12]

I am getting the message,"This function is not valid" and high lighting "....ERROR..."

[Glenn Kennedy]

With which formula and in which version of Excel?

[Glenn Kennedy]

If you are using Excel 2007 or later use the formula in G2; if you are using Excel 2003, use the formula in S2. Array enter (as described above) and drag accross then down.