After vlookup returns blank look left

[HairyHippy]

Is there a forumla that after doing a vlookup and that result is an empty cell that it looks back in the same row as the empty cell until it finds a cell with data?

For example
Sheet 1 has a list of currencies in column A and the other columns balances by month until say the end of this year.
Sheet 2 has the currencies in column A and the FX rates by month but only up until the current month, the remaing months of the year are blank.
These blanks will get filled with actuals.
Basically because I know what the balance will be in say July, I want to be able to convert that balance using the current rate or the July rate once known.
Put simply if July rate unavaible move back along row until a rate is found.

TIA

 

[SkipVought]

hi,

Please post an example of the lookup data that meets this condition and the lookup formula that you are using.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Why not use the COUNTA function to count the number of cells that have data?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HairyHippy]

Hi Skip

Can't upload a file from work unfortunately with the example data (or a mock up of it).

I'm using a vlookup formula, happy to switch to an index/match combo.

Basically the if statement would be something like:

=IF(vlookup(A2,sheet2!A:M,2,false)="",look left in same row on sheet 2 for first cell with data,vlookup(A2,sheet2!A:M,2,false))

It's the "look left in same row" forumla that I need.

Sample data would be column A has currencies on both Sheet 1 and Sheet 2. Columns B:M have the months on both sheets in row 1.
Row 2 on sheet 1 would be Australian Dollar followed by the balances by month
Row 3 on sheet 1 would be those same balances but in US Dollar equivalent. This is the line that would have the forumla
Row 2 on sheet 2 would have Australian Dollar followed by the exchange rates per month up to and including the current month but be blank for future months.

 

[HairyHippy]

It would be great if someone can suggest the formula to use but in the meantime I've found a work around.
Sheet 2 is actually a pivot table and therefore for the outter months, I can just make equal to the last month available, therefore simplfying the lookup formula and then when the next month's FX rate is available just allow the pivot table to overwrite the next column along.

 

[SkipVought]

[tt]
=VLOOKUP(A2,Sheet2!A:M,COUNT(OFFSET(Sheet2!$A$1,MATCH(A2,Sheet2!A:A,0)-1,0,1,12))+1,FALSE)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]