How do search a range to generate a value?

[Steve Hickman]

Period 0-7 41852 15-21 22-28
Value Time to pay
1000 21 [1000]
500 4 [500]

I am trying to set up a worksheet so that once an individual selects a value
in the Time to pay column, a value will be generated in the relevant period.
I've shown the desired output in square brackets above. I've tried an IF
function but I ultimately end up exceeding the available number of IFs
allowed. I've also tried lookups to no avail.

 

[Daryl S]

Steve -

You can put the formulas in the columns and drag them down. For example,
in the column "0-7", enter this:
=IF($B2 <= 7, $A2,0) (or you could use "" instead of zero)

For the column "8-14", enter this:
=IF(AND($B2 > 7,$B2 <= 14,$A2,0)

Same for the other columns. Then copy/paste or drag these formulas down.
You can format the cells to not show the zeros, or you can put blanks in
instead.