VBA modification VLOOKUP does not work on the right side

I had this question after viewing How to add a column of to the end of columns with value from table with VBA.

Rgonzo1971 was so kind helping me with the code below.

now when my data changed, (plz see attached) i could not fix the VLOOKUP thing.  my financial model sometimes data is in the right side and i cannot change the order.  now i am stuck. how can the VBA below be modified so that description is returned from the right column.

i would appreciate any help i can get here.

Sub macro()
Set ShD = Sheets("Data")
intProductCol = Split(Range("dataPRODUCT").Address, "$")(1)
FirstFreeCol = ShD.Cells(1, Columns.Count).End(xlToLeft).Column + 1
Set Rng = Range(ShD.Cells(2, FirstFreeCol), ShD.Cells(ShD.Range("a" & Rows.Count).End(xlUp).Row, FirstFreeCol))
Rng.Formula = "=IFERROR(VLOOKUP(" & intProductCol & "2,'Lookup Table'!$A$1:$B$6,2,FALSE),VLOOKUP(LEFT(" & intProductCol & "2,1)&""*"",'Lookup Table'!$A$1:$B$6,2,FALSE))"
Rng.Value = Rng.Value

End Sub

Open in new window

EE.xlsb
LVL 6
FloraAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
Sub macro()
Set ShD = Sheets("Data")
strProductCol = Split(Range("dataPRODUCT").Address, "$")(1)
FirstFreeCol = ShD.Cells(1, Columns.Count).End(xlToLeft).Column + 1
Set Rng = Range(ShD.Cells(2, FirstFreeCol), ShD.Cells(ShD.Range("a" & Rows.Count).End(xlUp).Row, FirstFreeCol))
Rng.Formula = "=INDEX('Lookup Table'!$B$1:$B$6,IFERROR(MATCH(" & strProductCol & "2,'Lookup Table'!$C$1:$C$6,0),MATCH(LEFT(" & strProductCol & "2,1)&""*"",'Lookup Table'!$C$1:$C$6,0)))"
Rng.Value = Rng.Value

End Sub

Open in new window

Regards
1
 
FloraAuthor Commented:
Thanks a billion Rgonzo1971.  because of people like you, the world is a better place live.
0
 
FloraAuthor Commented:
even words cannot describe how grateful i am to you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.