ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Hi,
I have an issue im not sure how to fix but will explain the best i can.
To start with i have this value JH2RC46A23M500753 "always 17 characters"
It is then pasted into cell F7 & with the cells format it is then shown like so JH2 RC46A 2 3 M 500753
Each partial piece of the value is then copied to various cells on row 9
So JH2 is in cell B9 etc etc
My concern is the partial piece of code RC46A which is shown in cell E9
Basically the last character of the value isnt important so in this case its the A & could solve my problem if the last character "A" does not show in cell E9 ???
THE ABOVE EXPLAINS THE WORKINGS.
THE BELOW SHOWS MY PROBLEM.
Cell E9 will always show a 5 character value taken from the 17 charcater value thats pasted into cell F7
Like mentioned in this case E9 shows RC46A
Cell F9 has the following VLOOKUP code.
It looks in column E for the value.
As you can see column E only shows 4 characters so if i change the VLOOKUP code to FALSE then i get a miss match error hence why ive had to put TRUE
Using TRUE finds the value in column E BUT the returned value isnt always the FIRST of its type.
PLEASE SEE ATTACHED SCREEN SHOT.
The first instance of value RC46 is row 993 where the last instance is row 1007 BUT for some issue the VLOOKUP selects the instance somewhere in between.
If the first instance was shown etc my problem would be fixed.
This then takes me back to NOT have the 5th character being shown in cell E9
Then the VLOOKUP would look for RC46 & by using TRUE would return the first value for me
I have an issue im not sure how to fix but will explain the best i can.
To start with i have this value JH2RC46A23M500753 "always 17 characters"
It is then pasted into cell F7 & with the cells format it is then shown like so JH2 RC46A 2 3 M 500753
Each partial piece of the value is then copied to various cells on row 9
So JH2 is in cell B9 etc etc
My concern is the partial piece of code RC46A which is shown in cell E9
Basically the last character of the value isnt important so in this case its the A & could solve my problem if the last character "A" does not show in cell E9 ???
THE ABOVE EXPLAINS THE WORKINGS.
THE BELOW SHOWS MY PROBLEM.
Cell E9 will always show a 5 character value taken from the 17 charcater value thats pasted into cell F7
Like mentioned in this case E9 shows RC46A
Cell F9 has the following VLOOKUP code.
Code:
=IFERROR(VLOOKUP(E9,E11:F1518,2,TRUE),"")
It looks in column E for the value.
As you can see column E only shows 4 characters so if i change the VLOOKUP code to FALSE then i get a miss match error hence why ive had to put TRUE
Using TRUE finds the value in column E BUT the returned value isnt always the FIRST of its type.
PLEASE SEE ATTACHED SCREEN SHOT.
The first instance of value RC46 is row 993 where the last instance is row 1007 BUT for some issue the VLOOKUP selects the instance somewhere in between.
If the first instance was shown etc my problem would be fixed.
This then takes me back to NOT have the 5th character being shown in cell E9
Then the VLOOKUP would look for RC46 & by using TRUE would return the first value for me