help with vlookup please!

[fry]

Hi All

I'm using the following

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

 

[Peter_SSs]

fry

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

 

[fry]

Thanks Peter that worked a treat!!

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

 

[Gerald Higgins]

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

Try this

=VLOOKUP(P5*1,Data!$E$1:Data!$F$89,2,FALSE)

This effectively converts the text value into a number.

 

[Gerald Higgins]

I'm far too slow today . . . . sigh

 

[fry]

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

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]

A bit long winded but try

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

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

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

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

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]

Hi Gerald

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

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