Vlookup or IF Statement Help

[corewin]

Hi all,
I need help with Vlookup
Currently I have a spreadsheet setup so that when I enter information in Column B on Column C looks up a vname (Which is on a different sheet) and puts the correct value in the box.
Everything works fine but I want to paste a large number of data into Column B and sometimes the info I paste will be missing a number at the end.
So for instants lets say I put in a35456 but its supposed to say a35456 01 I want to have it automaticly change column B and add the [space]01 to the end of it whenever column C has a value of #N/A. and then let me know that it did it by either copying that cell to another cell or highlighting it.
I don't know if this is possible.
If it isn't then maybe just have it so when it has an invalid entry (#N/A) in Column C it copies what I put in in column B to column A and adds [space]01 to it.
Currently the code in Column C is =VLOOKUP($B2,VName,2,FALSE)
I hope I explained that right
If any one has any ideas please let me know
Thanks

 

[tigeravatar]

corewin,

You could use this to correct the input from column B if it was input without the " 01":
=VLOOKUP(IF(RIGHT(B2,3)=" 01",B2,B2&" 01"),vname,2,FALSE)

As for letting you know when that happened, you'd have to use conditional formatting

 

[corewin]

Thanks for the reply.
=VLOOKUP(IF(RIGHT(B2,3)=" 01",B2,B2&" 01"),vname,2,FALSE)
Kind of works
The data i'm imputing will not have the " 01" at the end.
And only some of the them are supposed to have " 01" at the end the rest are as they supposed to be
So when I use this code
=VLOOKUP(IF(RIGHT(B2,3)=" 01",B2,B2&" 01"),vname,2,FALSE)
It fixes the issue with the ones that are supposed to have " 01" at the end but now it shows #N/A for the ones that do not have a " 01" at the end.
Any one have a solution?
Also if someone knows how I can use conditional formatting to highlight a cell if it made changes the the above formula that would be great.
Thank you guys

 

[AMAS]

If you have Excel 07 or 10 then this might do the trick:

=IFERROR(VLOOKUP(IF(RIGHT(B2,3)=" 01",B2,B2&" 01"),vname,2,0),VLOOKUP($B2,VName,2,0))

AMAS

 

[corewin]

That worked
Thanks
Now the only thing I have to figure out is how I can create an conditional formula or something so I know that it had to make the change.

 

[AMAS]

Using conditional formatting, under the options state to highlight cells that 'ends with' 01 and give a special format (e.g. yellow).

AMAS

 

[corewin]

Normally that would work.
But the formula

=IFERROR(VLOOKUP(IF(RIGHT(B2,3)=" 01",B2,B2&" 01"),vname,2,0),VLOOKUP($B2,VName,2,0))

Does not technically add " 01" to B2 so the number in B2 does not change.