# match function

#### Herbiec09

##### Active Member
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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"))

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

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

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

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

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

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.

Replies
3
Views
527
Replies
6
Views
239
Replies
9
Views
302
Replies
2
Views
197
Replies
9
Views
215

1,196,131
Messages
6,013,633
Members
441,777
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back