Index Match with Right Function

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
Hello all,

I am working on a football squares pool and I am having a hard time with the syntax for the index match function below is a link to a forum question from a while back that is very close to my issue.
however they are triming the USD after the index,match

I need to trim the number in the cell to the last digit and I am using the =right(cell,1) to return the last digit from the score and trying to use it in my index, match but it returns a N/A. I am guessing that the value of the cell is not the same as the number returned from the formula for some reason =INDEX(B2:L12,MATCH((RIGHT(O3,1)),B2:B12,0),MATCH(RIGHT(P3,1),C2:L2,0))

if I replace the right() function and hard code the two numbers it works fine.

in the first match function, I tried to use (( to have it evaluate the right function first and return a number and in the second match, I left it with just one ( neither one works.

I additionally used helper cells for the two scores =right( function) but that did not help either.

What am I missing or is there a different formula I need to be looking at.

Thank you
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,783
Very likely your issue is that the RIGHT function returns a text value, even if it starts with a numeric value. So if you start with 14, RIGHT(14,1) returns "4", and "4" is not the same as 4. So to convert the text "4" to a numeric 4, use RIGHT(O3,1)+0 instead of just RIGHT(O3,1). Adding 0 to a number saved as text converts it back to a number.
 
Solution

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
=INDEX(B2:L12,MATCH(RIGHT(O3,1)+0,B3:B12,0)+1,MATCH(RIGHT(P3,1)+0,C2:L2,0)+1)

I knew it was something about how the Right function was being evaluated just could not put my finger on it. thank you very much works great.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top