Auto populate

Copper Contributor

I have 2 cells, cell 1 will have 1 of 3 different numbers in it ($35, $45 or $50). I would like a cell 2 to populate 15 if cell 1 is $35, 20 if cell 1 is $45 or 25 if cell 1 is $50. Is there a formula that will do this? Please help as I have tried everything I can think of....

 

Thank you in advance :) 

5 Replies

@Megan2804 

Let's say the first cell is A2.

In the other cell, enter the formula

=IF(A2="","",IF(A2=35,15,A2-25))

Yes! Is there a way to keep it blank or 0 if nothing is entered into cell 1?

@Megan2804 

The formula already does that.

@Megan2804 

Just so that you may have choice!

= SWITCH(list,
      35, 15,
      45, 20,
      50, 25,
      "")

= IFERROR(
    LOOKUP(list, {35,15;45,20;50,25}),
  "")

= XLOOKUP(list, numbers, output,"" )

Of these only the 2nd works with Excel versions older than 2019.

@Megan2804 

If to hardcode the logic

=5*( 3+INT((A1-35)/7.5) )

and other variants in this direction