VLOOKUP ---> Assistance Needed

[Himansu]

Morning everyone,

Is it possible to use vlookup by trimming values in a column. It's a little
hard to explain, but here's an example:


Worksheet 1:

column1 column2
--------- ---------
hha1xxx "matched value from worksheet 2"

--------------------------------------------------------------------------
Worksheet 2:
colum1 column2
--------- ---------
hha2xxx 845


---------------------
I want to match by hha2xxx by extracting "hha"&2&"xxx" in worksheet1. Is
this possible? Here's what the result should be:

Worksheet 1:

column1 column2
--------- ---------
hha1xxx 845


** Please advise.

 

[Bernard Liengme]

Did you try it?

This worked for me
=VLOOKUP(LEFT(A1,3)&"2xxx",Sheet2!A1:B10,2,FALSE)
As did
=VLOOKUP(LEFT(A1,3)&J1&"xxx",Sheet2!A1:B10,2,FALSE)
with value of 2 in J1

best wishes

 

[paulgu]

Himansu,
The Help says "Lookup_value can be a value, a reference, or a text string" so there's nothing to stop you from deriving hha2xxx from hha1xxx and using hha2xxx as your lookup_value.
Rgds,
Paul

 

[Himansu]

Hello Bernard,

This was th eformula I was thinking of:

vlookup by Left(A15,4)&"2"&Right(a15,len(a15)-5) to get key count for Final
Mail Quantity 2 (new column N)

** How can I embed this into a vlookup formula?

 

[Bernard Liengme]

This worked form me:
=VLOOKUP(LEFT(A15,3)&"2"&RIGHT(A15,LEN(A15)-4),Sheet2!A10:B16,2,FALSE)

note your 4->3, and your 5 ->4

Do you know how to use Evaluate Formula to see how a formula 'evolves'

best wishes

 

[Himansu]

Morning Bernard,

I tried to insert this formula into my macro, but did not work:

-----------------
Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(left(RC[-1],4) & "2" &
right(a1,len(LEN(RC[-1])-5))," & JobNumber &
"_KEYCODES!R1C1:R150C3,3,FALSE)"