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.
Last edited by jrtaylor; 08-18-2011 at 12:36 AM. Reason: solved
Hello jrtaylor,
The formula to solve the problem in cell K4 is this...=VLOOKUP(K4,A13:C32,3,FALSE)*K4*(INDIRECT("B" & ROW(K4)))
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
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
Last edited by jrtaylor; 08-17-2011 at 09:48 PM.
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)
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
OK thanks so much!
Wish I could help in return but I'm getting old and forgetful!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks