Xmatch function

[sksanjeev786]

Hi Team

Can we have a data like column "D"

Book1
	A	B	C	D
1				Solution
2	Ad Fanta	1	=XMATCH($A2,$A$2:$A$10,0,1)	1
3	Ad Fanta	9	=XMATCH($A3,$A$2:$A$10,0,-2)	2
4	Ad Fanta	9		3
5	Ad Fanta	9		4
6	Ad Fanta	9		5
7	Ad Fanta	9		6
8	Ad Fanta	9		7
9	Ad Fanta	9		8
10	Ad Fanta	9		9
Sheet2

Cell Formulas
Range	Formula
C2:C3	C2	=FORMULATEXT(B2)
B2	B2	=XMATCH($A2,$A$2:$A$10,0,1)
B3:B10	B3	=XMATCH($A3,$A$2:$A$10,0,-2)

Thanks in advance
Sanjeev.

 

[Peter_SSs]

Can you explain in words exactly what you are trying to do?

Also, can you clarify about using XMATCH? Your profile says you have Excel 2016 where XMATCH is not available.

 

[jasonb75]

Is this what you mean?

Book1
	A	B	C	D
1				Solution
2	Ad Fanta			1
3	Ad Fanta			2
4	Ad Fanta			3
5	Ad Fanta			4
6	Ad Fanta			5
7	Ad Fanta			6
8	Ad Fanta			7
9	Ad Fanta			8
10	Ad Fanta			9
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=COUNTIF(A$2:A2,A2)

 

[sksanjeev786]

Can you explain in words exactly what you are trying to do?

Also, can you clarify about using XMATCH? Your profile says you have Excel 2016 where XMATCH is not available.
View attachment 26497

Hi Peter,

In my pc i have office 365 as I was using Xmatch function so I got +1 and -2 instant so I was looking for other option.

so with the help of Count if, i got the solution I think it is not possible with the XMATCH function.

Thanks for your help
Sanjeev

 

[Peter_SSs]

Hi Peter,

In my pc i have office 365

In that case why don't you update your forum profile to show that so that helpers can give you answers appropriate to your version in the future?

I think it is not possible with the XMATCH function.

Yes, that is not what XMATCH is designed for.

 

[sksanjeev786]

In that case why don't you update your forum profile to show that so that helpers can give you answers appropriate to your version in the future?


Yes, that is not what XMATCH is designed for.

Sure thanks for the update.