VLookup question

rollingzep

Board Regular
Joined
Nov 18, 2013
Messages
214
Office Version
  1. 365
Platform
  1. Windows
I have a value, PTBCPWOM003. To do a lookup on another sheet which has the actual value as BCPWOM003, how to modify my Vlookup.

There are quite a few entries which have the first two characters missing. How to do a Like or a mid or len to compare BCPWOM003 against PTBCPWOM003?

something like , If value of C1 = PTBCPWOM003 then (MID(C1,3,9)) = BCPWOM003

Currently, I use a VLookup

VBA Code:
 ws.Range("BE2:BE" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",HQLA!C1:C4,4,0)"

Few values in AG have two characters missing but in the second sheet, HQLA they have the complete value.
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
If your value will always have either 11 or 9 characters, you can do
=IF(LEN(C1) = 11, MID(C1,3,9),C1)
If your value could have varying number of characters, you will need to provide more information.
 
Upvote 0
If your value will always have either 11 or 9 characters, you can do
=IF(LEN(C1) = 11, MID(C1,3,9),C1)
If your value could have varying number of characters, you will need to provide more information.
C1 has 12 characters.
How do I use your formula inside my VLookup, ws.Range("BE2:BE" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",HQLA!C1:C4,4,0)"

Also, a secondary question...

Can I use the above VLookup with your formula as an IF?
Meaning...If C1 is 12 and suppose C3 is 9, Can I use both Columns in my VLOOKUP?
 
Upvote 0
If your value will always have either 11 or 9 characters, you can do
=IF(LEN(C1) = 11, MID(C1,3,9),C1)
If your value could have varying number of characters, you will need to provide more information.
How do I use this inside a VLookup as in

ws.Range("BE2:BE" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",HQLA!C1:C4,4,0)"
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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