Lookup from multiple columns

[rollingzep]

Hi,

For my sheet, I need to lookup values from two different columns.

Using one column, I get values.

ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C1:C35,35,0)"

Also, I need to read it from the Column, C3 of HQLA.

How to use the below along with the above

ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C3:C35,35,0)"


Using C1, I get values like IG-1, N-2.




For missing values, "#N/A",I need it to read from Col, C3.

 

[rollingzep]

I agree but I am using my code for other features in addition to this Vlookup.
That is why I need it in VBA since it has more than 1500 to 2000 rows

LEN is an EXCEL function so you can use it in cell of a worksheet. So there is no need to use VBA. I was suggesting using it to combine the two lookup into one cell.
To do the entire column just copy it down.

I tried
ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).Formula = _
"=IF(Len(Y2>9),(VLOOKUP(HQLA!C3:C33,33,0)),VLOOKUP(HQLA!C1:C35,35,0))"

But I am getting a #VALUE error

 

[offthelip]

I sugest you try each lookup equation on their own in two separate columns or do it twice in succession which each lookup equaiton