Need offset in Match / index function

[andreas.wpv]

I use index(match) functions to compare two lists.


Now I want to use not always the same range for my this, but a shifting range:


cell G1: match("andreas", B1:F10) = 4


now I want


cell G2: match("Willi", B1+4:F10) =...


So the second range is based on the first match.... with only one of the coordinates changing.


Possible? If any without VBA?

 

[Nacky]

Hi and Welcome Andreas,


Try this:


=MATCH('andreas',INDIRECT(CONCATENATE("B",ROW(OFFSET(B1,2,)),":F10")),0)


You can change the "2" Value in the offset to the increment you want, or point it to a value in a range.


Regards

 

[andreas.wpv]

Thank you Nacky!

I had to change it a bit, the row / offset did not work. so I ended up just with


IF(L2>=2,SUM(N2,MATCH(J2,INDIRECT(CONCATENATE("$A",(N2+2),":$A$24")),0)),0)


where N2 is based on the first match (looking for 2nd match to then index)


Never encountered indirect before, very helpful.