Index Match with Right Function

[Drrellik]

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

Index Match with Right Function

I have an index match that gives me results for prices with a string of text I want to remove. Example: USD 41.99 In a separate cell I have the RIGHT function to remove the "USD ", but I would like to have a single formula in one cell...

www.mrexcel.com

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

 

[Eric W]

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.

 

[Drrellik]

=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.