Forum Discussion

Brass Contributor

Mar 15, 2022

Solved

using cell reference instead of cell contents Help

Hi Excel gurus, any chance you can assist a newbie like me to find a good formula to replace this with cell reference instead of using the actual number thats in the cell as the number will vary from time to time

Works good so far but want to be able to change the numbers, i have highlighted the numbers, not sure what the correct formula is to use to capture the actual cell reference...

excel

Formulas and Functions

JMB17
Mar 15, 2022
Marcel55

Not with the match function. You could use nested if functions (say your values are in A1, B2, C3, and D4):
=IF(D4<=D10, S10, IF(C3<=D10, P10, IF(B2<=D10, M10, IF(A1<=D10, H10, NA()))))*D10

Or, if you have the IFS function, you could try:
=IFS(D4<=D10, S10, C3<=D10, P10, B2<=D10, M10, A1<=D10, H10, True, NA())*D10

4 Replies

JMB17
Bronze Contributor
Mar 15, 2022
Marcel55

You can reference a range with the first cell address, then a colon, then the second cell address. So, if you had:
A1 = 0
A2 = 20
A3 = 50
A4 = 100

then your formula would be:
=CHOOSE(MATCH(D10, $A$1:$A$4), H10, M10, P10, S10)*D10
- Marcel55
  Brass Contributor
  Mar 15, 2022
  HI JMB many thanks for your reply, does this still count if the cells are not next or grouped together, i.e could you use $A$1 + $B$2$ for instance....
  - JMB17
    Bronze Contributor
    Mar 15, 2022
    Marcel55
    
    Not with the match function. You could use nested if functions (say your values are in A1, B2, C3, and D4):
    =IF(D4<=D10, S10, IF(C3<=D10, P10, IF(B2<=D10, M10, IF(A1<=D10, H10, NA()))))*D10
    
    Or, if you have the IFS function, you could try:
    =IFS(D4<=D10, S10, C3<=D10, P10, B2<=D10, M10, A1<=D10, H10, True, NA())*D10