Advice for vlookup code to show first instance

[ipbr21054]

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.

=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

 

[Fluff]

How about
=IFERROR(VLOOKUP(LEFT(E9,4),E11:F1518,2,0),"")

 

[DanteAmor]

try this:
=IFERROR(VLOOKUP(LEFT(E9,4),E11:F1518,2,FALSE),"")

 

[ipbr21054]

Thanks.
Both work frine.
The only difference i can see in these two codes is the 2 & false towards the end.

Is 2 the same as FALSE where 1 would be the same as TRUE ?

 

[ipbr21054]

Also while im here what if the code looks but dont find the item in the column.
As opposed to seeing the Run time error 13 Type Mismatch.

The code in use is as follows.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strArr() As String
  If Target.Address = "$F$7" And Len(Range("F7").Value) = 17 Then
    Range("F7").Value = Format(Range("F7").Value, "&&& &&&&& & & & &&&&&&")
    strArr = Split(Range("F7").Value, " ")
   Range("B9").Value = strArr(0)
   Range("E9").Value = strArr(1)
   Range("J9").Value = strArr(2)
   Range("L9").Value = strArr(3)
   Range("O9").Value = strArr(4)
   Range("R9").Value = strArr(5)
   Range("F7:H7").Select
   Selection.HorizontalAlignment = xlCenterAcrossSelection
   Range("A1").Select
           With ActiveWindow
        .ScrollColumn = ActiveCell.Column
        [COLOR=rgb(226, 80, 65)].ScrollRow = Range("C1").Value[/COLOR]
    End With
End If

The error happens as the item wasnt found so the scroll does happen, can we have a MsgBox if upon an error ??

Just a thought

 

[Fluff]

The only difference i can see in these two codes is the 2 & false towards the end.

The only difference is the 0 & False, not 2 & False. And Yes 0=False 1=True.

Please start a new thread for your new question as it's completely different.

 

[ipbr21054]

Sorry my mistake,yes 2 & False.

I have been using this code this morning but see one issue.

=IFERROR(VLOOKUP(LEFT(E9,4),E11:F1518,2,FALSE),"")

There is one 17 character code that causes the incorrect value to be shown etc, Please see screen shot.
The cell E9 shows SC50A
This should then select the first instance of SC50 being row 1271 but every time it selects SC28 on row 1197

 

[Fluff]

What is the first value for SC50?

 

[ipbr21054]

To the right of the first SC50 which is cell E1270 there is CBR900RR at cell F1270

As a test i changed the range in the VLOOKUP code to E1260:F1518 expecting it to select the correct item at row 1270 but it still decided to select 1197, so it ignored the range told to look in

 

[RoryA]

so it ignored the range told to look in

That categorically will not happen, so there must be something else going on.