RIGHT will not work in formula.

[S.H.A.D.O.]

Good afternoon,

I have the formula:

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

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

Thanks in advance.

 

[Jonmo1]

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})

 

[Norie]

RIGHT (and LEFT and MID) returns a string.

 

[S.H.A.D.O.]

Thanks Jonmo1 & Norie that sorted it.

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

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

 

[Aladin Akyurek]

Thanks Jonmo1 & Norie that sorted it.

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

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

 

[Ruddles]

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.

 

[Jonmo1]

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

 

[S.H.A.D.O.]

Thanks everyone, have a great weekend.