match function

[Herbiec09]

Hi all,

Need some help with the match function. Normal syntax is match("value",b2:k2,0). I would like the formula to read =match("value",k2:b2,0), so that excel counts backwards from k towards b.

Would anybody know how i can achieve this?

Thanks

Herbert

 

[Aladin Akyurek]

Hi all,

Need some help with the match function. Normal syntax is match("value",b2:k2,0). I would like the formula to read =match("value",k2:b2,0), so that excel counts backwards from k towards b.

Would anybody know how i can achieve this?

Thanks

Herbert

Control+shift+enter, not just enter:

=MATCH(9.99999999999999E+307,1/(B2:K2="value"))

 

[Gigi_]

also, try this
=11-match("value",b2:k2,0)

 

[Herbiec09]

Control+shift+enter, not just enter:

=MATCH(9.99999999999999E+307,1/(B2:K2="value"))

Thank you so much for this, is there a quick way of inserting th "..9.99999999..........." part?

Thanks

 

[Aladin Akyurek]

Thank you so much for this, is there a quick way of inserting th "..9.99999999..........." part?

Thanks

You can define BigNum as referring to:

=9.99999999999999E+307

and use this definition in the formulas:

=MATCH(BigNum,1/(B2:K2="value"))

Another option is to invoke its contracted equivalent:

9.99E+307

 

[Herbiec09]

You can define BigNum as referring to:

=9.99999999999999E+307

and use this definition in the formulas:

=MATCH(BigNum,1/(B2:K2="value"))

Another option is to invoke its contracted equivalent:

9.99E+307

Hi Aladin,

Thanks for the formula above, but this produces an #DIV/O error, I have tried to use it as follows: =MATCH(BigNum,1/(B8:M8="jhb"))

Any suggestions?

Thanks

Herbert

 

[Aladin Akyurek]

Hi Aladin,

Thanks for the formula above, but this produces an #DIV/O error, I have tried to use it as follows: =MATCH(BigNum,1/(B8:M8="jhb"))

Any suggestions?

Thanks

Herbert

You need to confirm this formula with control+shift+enter, not just enter.That is: Press down the control and shift keys at the same time, while you hit the enter key.