range change

[Peterfc2]

How can I change the ranges so i can drag across the cells A1, B1,C1,as shown?

MATCH($A1,$H$1:$H$4,0)

MATCH($A1,$H$9:$H$12,0)

MATCH($A1,$H$17:$H$21,0)

 

[Peter_SSs]

What cells are those formulas actually in?
Can't be in A1, B1, C1 as A1 is used in the formulas.

 

[Peterfc2]

C1,D1,E1. My data is H.

 

[Peter_SSs]

I have assumed that the range $H$17:$H$21 in your final formula in post 1 is an error as that range is 5 cells whereas the first two ranges are only 4 cells.

If that is correct and that should have been H17:H20 then try this in C1 copied across to D1, E1

=MATCH($A1,OFFSET($H1,COLUMNS($C1:C1)*8-8,0,4),0)

If you wanted to avoid the volatile function OFFSET then you could use this non-volatile alternative.

=MATCH($A1,INDEX($H:$H,COLUMNS($C1:C1)*8-7):INDEX($H:$H,COLUMNS($C1:C1)*8-4),0)

 

[Peterfc2]

Sorry example was poor and badly explained. So new approach. Picture of data.
=MATCH($A2,sortuniques!$A$3:$A$21,0)
=MATCH($B2,sortuniques!$A$26:$A$44,0)
=MATCH($C2,sortuniques!$A$49:$A$67,0)
so D2 should be
=MATCH($D2,sortuniques!$A$72:$A$90,0)

ranges steps are constant 23 and 18. I have hard coded B2.C2.D2
I have dragged b2 down to B20 etc.
Need to drag b2 across the columns.
Hope that explains it better.

 

[Peter_SSs]

so D2 should be
=MATCH($D2,sortuniques!$A$72:$A$90,0)

I don't think so - referencing D2 in a formula in D2 would cause a circular reference.

=MATCH($A2,sortuniques!$A$3:$A$21,0)
=MATCH($B2,sortuniques!$A$26:$A$44,0)
=MATCH($C2,sortuniques!$A$49:$A$67,0)

It is not clear whether they are formulas you have that are not working or formulas you want. Further, there is no indication of what cells they are or would be in.
Can you carefully insert the exact formulas you want in cells B2:D4 (9 cells) in your sheet, check that they are returning the results you expect, and then post those 9 formulas here, indicating which cell each formula is in.

 

[Peterfc2]

My error again.
=MATCH($A2,sortuniques!$A$3:$A$21,0)
=MATCH($A2,sortuniques!$A$26:$A$44,0)
=MATCH($A2,sortuniques!$A$49:$A$67,0)
so D2 should be
=MATCH($A2,sortuniques!$A$72:$A$90,0)

 

[Peter_SSs]

My error again.
=MATCH($A2,sortuniques!$A$3:$A$21,0)
=MATCH($A2,sortuniques!$A$26:$A$44,0)
=MATCH($A2,sortuniques!$A$49:$A$67,0)
so D2 should be
=MATCH($A2,sortuniques!$A$72:$A$90,0)

For the first 3 formulas above this is still the case:

there is no indication of what cells they are or would be in

There is also no response to my request for a few formulas from a few rows so that I can see what changes and what doesn't.

 

[VBasic2008]

In B2 use

=IFERROR(MATCH($A2,INDEX(sortuniques!$A:$A,COLUMNS($B$1:B$1)*23-20):INDEX(sortuniques!$A:$A,COLUMNS($B$1:B$1)*23-2),0),"")

and copy across.

 

[Peter_SSs]

In B2 use

I suspect that you may well be right, but that doesn't get the D2 formula to be matching in rows 72:90 as indicated by the OP in posts 5 and 7. I am guessing that the OP is just not being careful enough with precise information which is why I was trying to press that point.