Using VLOOKUP with RIGHT function..

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

Good evening,

I have text in cell I6 of the current Worksheet with a number at the end of it.

Some sort of text here [B]2,126[/B]

I want to use the number 2,126 in the text above to VLOOKUP the number 2,126 in another Worksheet.
Here is the formula I am using but can't seem to get it to work.

=CONCATENATE(TEXT (VLOOKUP(RIGHT(I6,5),Master!B16:D2515,3)," dd-mmm-yyyy"))

Any help will be appreciated.

 

[Marcelo Branco]

I don't know why you are using CONCATENATE - probably you have showed only part of your formula.
Try to convert the lookup_value to a number
RIGHT(I6,5)+0

M.

 

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

Brilliant, thanks M.

I did try using...

=CONCATENATE(TEXT(VLOOKUP(--RIGHT(I6,5),Master!B16:D2515,3)," dd-mmm-yyyy"))

...among other things but couldn't get it to work.

Thanks again, it is appreciated.

 

[Marcelo Branco]

You are welcome.

--RIGHT(I6,5) also worked for me

M.

 

[Jonmo1]

Can you describe what "couldn't get it to work" means ?

Are you getting an error? What error?
Do you get the wrong result ? What result DO you get? How is that different from what you expected ?

 

[CodeNinja]

Hello S.H.A.D.O,
I believe the problem you are having is the lookup value is read by excel as a text value. Try using the VALUE formula to fix this problem:

=CONCATENATE(TEXT (VLOOKUP(VALUE(RIGHT(I6,5)),Master!B16:D2515,3)," dd-mmm-yyyy"))

Good luck,

CN.

 

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

Thanks to everyone for the answers, it is appreciated.
I also tried the --RIGHT again this evening and it worked, very strange.
All working nicely now, thanks again.