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))
=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))
- Thanks a lot! this is exactly what I needed. I will give you the best response since your message was first 😄 hope that mtarler is ok with this. Both solutions are valid here
