VLookup using concatenated lookup value to a table on a second sheet

  • I have a working VLookup formula placed into cell B3 in Table 1, copied below. The formula uses a concatenation of: $A3 & "_" & C$2 as the lookup value. Table 2 on Sheet2 has the same concatenation in column C, as shown on the attached 'Book1'.

    =VLOOKUP($A3 & "_" & C$2, Sheet2!$C$3:$D$8, 2, FALSE)


    I have tried to adapt this formula for use within VBA code, but have not hit on the right version.


    Assistance gratefully received.


  • Hello,


    Can you explain in a few words your expected result ... and why you would a macro for a formula which is already working fine ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you Carim. This is a small part of a large data manipulation and analysis solution within an Excel VBA workbook. I have a larger version of these two tables that are updated dynamically, and the VLookup formula is used to place key information from one data set beside another data set.


    VBA formulas I have tried include:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1" & ""_"" & "R3C[1], Sheet2!R3C3:R8C4," & "2, FALSE)"

  • ... after coming back to this, I worked again on the idea that the trick is that the ampersands are used two different ways: to concatenate the lookup value, and to string the formula together.


    This works

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1" & "& ""_"" &" & "R3C[1], Sheet2!R3C3:R8C4," & "2, FALSE)"


    Regards and thanks.

  • Glad to hear you could solve your problem :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!