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...

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

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
  - Marcel55
    Brass Contributor
    Mar 15, 2022
    Hi JMB,
    Wow thanks for this it is very much appreciated works a treat, just made my day.
    Regards