#### fry

##### Active Member
Hi All

I'm using the following

Code:
``VLOOKUP(P5,Data!\$E\$1:Data!\$F\$89,2,FALSE)``

If I enter "313" in P5 I get the correct value in the cell above but if I enter RIGHT(F5,3) in P5, which is the same I get #N/A??????????

Help...

P.S. F5 = "ABC/123/313 therefore RIGHT(F5,3) = 313 ????????

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Peter_SSs

##### MrExcel MVP, Moderator
fry

In P5, try =RIGHT(F5,3)+0
to convert the result from text to a number.

#### fry

##### Active Member
Thanks Peter that worked a treat!!

Is that how to convert any text to a number for future reference ??

#### Gerald Higgins

##### Well-known Member
I think this is because it's treating the RIGHT(f5,3) as text, and the other value as a number.

Try this
Code:
``=VLOOKUP(P5*1,Data!\$E\$1:Data!\$F\$89,2,FALSE)``

This effectively converts the text value into a number.

#### Gerald Higgins

##### Well-known Member
I'm far too slow today . . . . sigh

#### fry

##### Active Member
Thanks Guys, I wouldn't mind some help to refine this...

Code:
``IF(F7="","",VLOOKUP(RIGHT(F7,3)+0,Data!\$E\$1:Data!\$F\$161,2,TRUE))``

This is now OK as long as there is a value that matches to produce a lookup but I've found that sometimes I can get 2 more results.......ABC or XYZ which don't have a lookup.

How would I deal with that in the formula if I wanted for example to get the result ONE when it finds ABC and the result TWO when it finds XYZ????

Thanks again

#### Gerald Higgins

##### Well-known Member
A bit long winded but try
Code:
``=IF(F7="","",if(VLOOKUP(RIGHT(F7,3)+0,Data!\$E\$1:Data!\$F\$161,2,TRUE)="ABC","ONE",if(VLOOKUP(RIGHT(F7,3)+0,Data!\$E\$1:Data!\$F\$161,2,TRUE)="XYZ","TWO",(VLOOKUP(RIGHT(F7,3)+0,Data!\$E\$1:Data!\$F\$161,2,TRUE)))))``

#### fry

##### Active Member
OK, I've managed to get so far but now I'm getting a #VALUE result...

Code:
``=IF(F5="","",IF(RIGHT(F5,3)="GEN","N/A",IF(RIGHT(F5,3)="BEDS","Bedford",VLOOKUP(RIGHT(F5,3)+0,Data!\$E\$1:Data!\$F\$161,2,FALSE))))``

Can anyone spot any mistakes???

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks Guys, I wouldn't mind some help to refine this...

Code:
``IF(F7="","",VLOOKUP(RIGHT(F7,3)+0,Data!\$E\$1:Data!\$F\$161,2,TRUE))``

This is now OK as long as there is a value that matches to produce a lookup but I've found that sometimes I can get 2 more results.......ABC or XYZ which don't have a lookup.

How would I deal with that in the formula if I wanted for example to get the result ONE when it finds ABC and the result TWO when it finds XYZ????

Thanks again
This is not clear to me. Is there any chance of posting some sample data and explain in relation to that sample data?

#### fry

##### Active Member
Hi Gerald

With mine I get 'N/A' for 'GEN' but a #VALUE for 'BEDS'

With yours I get #VALUE for both ????

Replies
4
Views
472
Replies
0
Views
320
Replies
1
Views
303
Replies
1
Views
109
Replies
4
Views
264

1,191,131
Messages
5,984,845
Members
439,918
Latest member
engrnoir

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

### Which adblocker are you using?

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

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