Advice for vlookup code to show first instance

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. 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.
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
 

Attachments

  • 6049.jpg
    6049.jpg
    151.4 KB · Views: 8
To the right of the first SC50 which is cell E1270 there is CBR900RR at cell F1270
Which is exactly what the Vlookup formula is returning, so it's working correctly. :)
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Im confused.


Follow this through with me please

Cell E9 shows SC50A

In cell F9 is the VLOOKUP code supplied
VBA Code:
=IFERROR(VLOOKUP(LEFT(E9,4),E11:F1518,2,FALSE),"")

The code then looks in column E & the first SC50 it finds copies the value from cell to the right in column F

This is then shown in cell F9
 
Upvote 0
Ok,
Im sorry mistake.

The issue would be this scroll to row code then.

VBA Code:
=IFERROR(MATCH(F9,F11:F1518,0) + 10,"")
 
Upvote 0
All that formula is doing is returning the row number of the 1st match for cell F9, It's not scrolling, so not sure what you mean.
If you simply want to find the 1st row that matches the value in E9, run your match formula on col E not col F
 
Upvote 0
OK
How is it written to forget the last character.
E9 shows SC50A but if the code currently looks for it nothing will be found.
The correctly writtem code needs to look for SC50 only

Need to add the (E9,4) into the code to only look for 4 characters
 
Upvote 0
You would need to match Left(E9,4) the same way as in the vlookup
 
Upvote 0
Could you advise if this is correct

VBA Code:
=IFERROR(MATCH(LEFT(E9,4),F11:F1518,0) + 10,"")
 
Upvote 0
You beat me to it

VBA Code:
=IFERROR(MATCH(LEFT(E9,4),E11:E1518,0) + 10,"")

Thanks for the continued support
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top