using lookup and match in dynamic range

I have a spreadsheet which I modify several times a week. I add columns to a table.

If the values in each cell of the right column meet a certain condition (e.g. < 500), then I need to multiply the number by (a) a number in the same row in a fixed column to the left, and (b) a factor in another (fixed) table which is probably obtained by either match or lookup.

To illustrate my question I've attached a sample worksheet.

I will greatly appreciate any help.

Thanks.

Hello jrtaylor,

The formula to solve the problem in cell K4 is this...

=VLOOKUP(K4,A13:C32,3,FALSE)*K4*(INDIRECT("B" & ROW(K4)))

Thank you so much!

Whoops, I marked it SOLVED before I realized I still need help with this formula.

Cell K4 is 'today's" entry. Tomrrow it will be cell L4. How do I look up the cells in the last column of a table?

Thanks Again

Hello jrtaylor,

Sorry for the delay. I was busy feeding my animals and eating dinner.

This formula can be added in cell "L4" and drug down. This will automatically update the values for the column to the left of the formula. This will keep working when a new column is added. However, if there are no values in the cells Excel will display an "#N/A" error in the cell.

This formula is volatile meaning it will be updated whenever any other cell is calculated. If your worksheet becomes quite large then the volatile functions may impact calculation speed negatively. Here is the formula...

=VLOOKUP(INDIRECT("RC[-1]",FALSE),A13:C32,3,FALSE)*INDIRECT("RC[-1]",FALSE)*INDIRECT("RC2",FALSE)