# RIGHT will not work in formula.

#### S.H.A.D.O.

##### Well-known Member
Good afternoon,

I have the formula:

Code:
``````=IF(SUMPRODUCT(COUNTIF(\$G22:\$K22,VLOOKUP(\$B\$3,Master!\$B\$16:\$U\$2016,{14,15,16,17,18,19})))
=3,VLOOKUP(3,\$B\$20:\$D\$27,{3}),"W")``````

...which works OK if I hard code the number into it.

But I want to replace the 3 with a cell reference where there are alpha characters at the beginning and then a space and then a SINGLE numeric value.
There are quite a few so getting the formula to work will save time.

So I changed the formula to:

Code:
``````=IF(SUMPRODUCT(COUNTIF(\$G22:\$K22,VLOOKUP(\$B\$3,Master!\$B\$16:\$U\$2016,{14,15,16,17,18,19})))
=RIGHT(F22,1),VLOOKUP(RIGHT(F22,1),\$B\$20:\$D\$27,{3}),"W")``````
...but it throws out a "W" instead of the correct figure.
As I said, the first formula gives me the correct result so I know that part of it is OK.

Ant help will be appreciated.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
The Right function returns a TEXT string, even if it's a numeric it becomes a "number stored as text"
Try adding 0 to the result of the RIGHT function..

VLOOKUP(RIGHT(F22,1)+0,\$B\$20:\$D\$27,{3})

RIGHT (and LEFT and MID) returns a string.

Thanks Jonmo1 & Norie that sorted it.

The strange thing is that after reading your replies I thought I would try this as well:

Code:
``````=IF(SUMPRODUCT(COUNTIF(\$G22:\$K22,VLOOKUP(\$B\$3,Master!\$B\$16:\$U\$2016,{14,15,16,17,18,19})))
=SUM(RIGHT(F22,1)),VLOOKUP(SUM(RIGHT(F22,1)),\$B\$20:\$D\$27,{3}),"W")``````

...and that worked, strange.

Thanks anyway and have a great weekend.

Thanks Jonmo1 & Norie that sorted it.

The strange thing is that after reading your replies I thought I would try this as well:

Code:
``````=IF(SUMPRODUCT(COUNTIF(\$G22:\$K22,VLOOKUP(\$B\$3,Master!\$B\$16:\$U\$2016,{14,15,16,17,18,19})))
=SUM(RIGHT(F22,1)),VLOOKUP(SUM(RIGHT(F22,1)),\$B\$20:\$D\$27,{3}),"W")``````

...and that worked, strange.

Thanks anyway and have a great weekend.

Note that:

SUM(RIGHT(F22,1)) is equivalent to RIGHT(F22,1)+0 or just RIGHT(F22)+0.

Any function which returns a numeric value would have worked, so SUM will do nicely. You could have added zero, multiplied or divided by one, used INT, etc.

Sum does pretty much the same thing as +0.
converts "number stored as text" to number

Thanks everyone, have a great weekend.

Replies
17
Views
401
Replies
9
Views
187
Replies
16
Views
264
Replies
1
Views
165
Replies
6
Views
207

1,203,642
Messages
6,056,507
Members
444,872
Latest member
Vishal Gupta

### 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?

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