# conditional lookup macro looping

#### xelhelp

##### New Member
hi,

in cell S8 of sheet2 im trying to do a calculation

in sheet1...

i have values in sheet 1 in column I, I2:I42
i have dates in sheet 1 in column J, J2:J42
i have values in sheet 1 in column K, K2:K42

in sheet2...

i have values in sheet 2 in column M, P, and G and dates in column E all of this data starts at row 5 going down X number of rows, however they will all go down the same number of rows, X because the external feed will update and I don't know how many rows it will populate

the dates in cell E5 of sheet2 could be entered anywhere between E5:EX depending on the update

i have a single value in cell R8 of sheet2
the final answer of the calculation would be in cell S8 of sheet2

the logic for the calculation is :

check date in column E of sheet2 with the dates in column J of sheet1, for each date in column J of sheet1 that is on or before the date in column E of sheet2 do the following calculation

(1 + ((value in sheet1 column I associated with date in column J - value in sheet2 R8) * (value in sheet1 column K associated with date in column J / 360))

depending on how many dates in column J of sheet1 that are on or before the date in column E of sheet2, this calculation above would be done multiple times

next... (1 + (value in sheet2 column P that is associated with the date in sheet2 column E - value in sheet2 R8) * (value in sheet2 column M associated with date in sheet2 column E/ 360))

multiply the result from the expression highlighted in green with each result from each of the blue expression, lets call this the denominator result...

so if four dates in column J of sheet1 are on or before the date in column E of sheet2 the "denominator result" would be

blue1 * blue2 * blue3 * blue4 * green

finally, (the value in sheet2 column G that is associated with the with date in sheet2 column E / denominator result)

the expression highlighted in red the the final value....for this particular date in sheet1 column E...

if the external feed populated only one date, thus column E had 1 date, this would be the final answer in cell S8... if we had four dates in column E in sheet2 the calculation is done four times and each result from the red expression is added, and that's the answer shown in cell S8

im not sure how to get started on this because the number of dates in column E is dynamic (i.e. depends on the update from external feed) and im not sure how the macro would be written that will use the correct dates and the associated values in sheet1 based on the date in sheet2 column E

thanks, any help is appreciated

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
i think it will require some sort of nested loops, with next statements but im not sure how to get started...

Replies
2
Views
148
Replies
1
Views
200
Replies
19
Views
1K
Replies
2
Views
312
Replies
1
Views
569

1,218,618
Messages
6,143,502
Members
450,492
Latest member
Rusbus1972

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back