Forum Discussion
Change cell reference formula row based on input
=IF(A3>50,A3/$J$3,IF(A3>100,A3/$L$3,0))What is the intended result in column B if the value in column A is greater 100? The above formula returns the result of A3/$J$3 for all values in column A which are greater 50. I suppose you want to return the result of A3/$L$3 if A3 is e.g. 250 and changed the formula:
=IF(A3>100,A3/$L$3,IF(A3>50,A3/$J$3,0))In order to select the row from Table2 i used INDEX and MATCH with your formulas for example the formula in column B is:
=IF(A3>100,A3/INDEX($L$3:$L$31,MATCH(G3,$I$3:$I$31,0)),IF(A3>50,A3/INDEX($J$3:$J$31,MATCH(G3,$I$3:$I$31,0)),0))
Alecs This is perfect use for a LOOKUP function. There are actually many ways to accomplish this. In the attached I use 2 versions both using VLOOKUP(). I would recommend XLOOKUP() if you have Excel 365 as it has improved performance but in case you don't have 365 I used Vlookup. In sheet 1 i use cell references and chose a range to row 99 for table 2. In sheet 2 I converted both tables into actual Tables and used the table structured reference, which will grow or shrink with the size of the table and probably a better way to do it (IMHO).
- Thank you Matt. This is exactly what I needed here. I much appreciate your time and efforts
wish you all the best! 🙂
