Multiply Range by Lookup Value Using Evaluate

[erock24]

Not sure it's possible, but I was trying to see if I could multiply a range of values by a lookup value using Evaluate to avoid writing a loop. Here is what I have, it doesn't work. It executes, but it appears to only multiply the range by the first value in the lookup table.

Please Login or Register  to view this content.

If I take out the IF(ROW I get the same answer. This code produces same result as above. Is it possible to do what I'm trying to do??

Please Login or Register  to view this content.

[dflak]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[erock24]

Example file now attached.

[dflak]

Can you assure that the years in column B will always match, row by row, the years in column J?

[erock24]

No. They won't always match. In Fact, I was hoping that if there was a solution, it could be converted to apply to a situation where the Original values are structured horizontally even. The Lookup table will always be vertical.

[dflak]

In that case, I really do not see an option other than looping. Or you can certainly use helper columns.

If you go with a vertical structure, you can use Excel tables to copy down the formulas automatically.

If you go horizontally, you'll have to copy over the formulas manually.

Which way would you like to go? Vertically with helper columns, horizontally with manual copy of formulas, VBA?

[erock24]

I'll just go VBA. I can handle writing the code for the loop. Thanks for checking.

[Marc L]

No need a loop just using a temp column :

Sub Demo1() 
    With Sheet2.[D3:D19] 
        .Formula = "=VLOOKUP(B3,$J$3:$K$19,2)*C3" 
        .Offset(, -1).Value = .Value 
        .Clear 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[xlnitwit]

For this particular case, since you have an entry for each year and the table is sorted, you might use

Please Login or Register  to view this content.