Search Loop Lookup

[Rinah]

In a nutshell, I have an Excel workbook that updates Exchange Rates, the workbook is driven by dates (fortnightly basis).
A historical worksheet shows the rates each fortnight – the date is the driver but the issue I am having is that when a rate has not changed I need the formula to search back each fortnight until the last change to the rate was made. I am not sure what formula to use here.

EG:
Fortnight 1 – Set rate;
Fortnight 2 – No change to Rate;
Fortnight 3 – No change to Rate;
Fortnight 4 – No change to Rate;
Fortnight 5 – change to Rate

Basically thinking in binary terms it would be: 1,0,0,0,1 – so if in fortnight 3 no change then it would pick up fortnight 2, but if this is no change, then goes back to Fortnight 1 (so like a loop until it reaches the last change that was made).

Any help would be much appreciated!

 

[steve the fish]

Hi. Give us an example of how the table of data is set up.

 

[Rinah]

Dates are in the top row
Data is in the columns

Date: 14-May 29-May 11-Jun 25-Jun
1 0 0 1

0 represents no change to rate
1 represents change to rate

So 11 Jun would pick up the rate from 14 May as the rate did not change on 11 Jun or 29 May
29 May picks up the rate from 14 May
25 Jun the rate changed so it picks up that rate from that date

I hope this makes more sense

 

[steve the fish]

So you want the last 1 in the row?

=LOOKUP(2,1/(2:2=1),1:1)

This will find the last 1 in row 2 and return whatever is in row 1.

 

[Rinah]

Sorry don’t think I was clear enough:

Table one has exchange rates by fortnight these change or remain the same

Table two has 0 and 1 - 1 represents a change in the rate for a particular fortnight, 0 represents no change to the rate in that fortnight

Table three looks up the value in table two to determine whether the rate changed or not; then takes the rate from table one as a result to the value in table two

The column B has a list of currencies, column C onwards has the exchange rate for the 1st fortnight, column has fortnight 2 rates etc until the end of the year

 

[steve the fish]

I dont think you were very clear if there are three tables! Your method seems convoluted. Do you have a table of currencies, dates and FX rates somewhere? It can all be done from that.

 

[Rinah]

Sorry again

I have an input sheet - fortnightly rates entered

A Calculation sheet that takes the rate from the input sheet then applies a 4% markup
It also checks if the mark up between the current fortnight and previous fortnight varies by 1% increase or decrease which determines whether the rate should be changed or not - table 1. This is represented by the 1 or 0 - table 2

Table 3 is a summary of the rates that were applied for a particular fortnight by checking table 2 and then returning the rate from table 1.

 

[steve the fish]

Got to say im lost. Perhaps now you really need to copy a representative sample from each table and post it here. Copy and paste it from excel. Dont type it or i need to type it and i dont like that That way i may understand what you are attempting to do.

 

[Rinah]

I have sent you a PM