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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,797
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,129,300
Messages
5,635,398
Members
416,856
Latest member
silentir

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