Vlookup with variable column reference

[jenVBA]

Help!
I am trying to link two workbooks with a vlookup that searches for the last
populated columns' entry in a range (see example below).

27/03 28/03 29/03 30/03 31/03
Data 6 4

Is there a way of doing this in a function? In a macro I would use the
Range().End(xltoleft) code but I cannot figure out how to do something
similar in a worksheet function. I basically want the lookup column to start
at day 31 and make its way backwards until it finds a value. Another issue is
that the seemingly blank cells contain formula - ie. not empty.

Any ideas?

Thanks!! Jen

 

[Paul C]

1 1-Jan 2-Jan 3-Jan 4-Jan
2 Data A 1 2
3 Data B 1 2 3
4
5
6 Data Lookup
7 Data B 3

The formula for the Lookup is
=VLOOKUP(A7,A1:E3,MATCH("",OFFSET(B1,MATCH(A7,A2:A3,0),0,1,4),0),FALSE)

This assumes that the "blank" cell formulsa are evaluating to "", a
suppressed 0 does not work for this.

The whole key is this
MATCH("",OFFSET(B1,MATCH(A7,A2:A3,0),0,1,4),0)

The Offset establishes a range for the Match ("",...) to use
the Match (A7,A2:A3,0) establishes how many rows down from B1 to start the
range (is this case 2). The 0 establishes 0 columns over from B1, the 1 is
the height or number of rows in the range and the 4 is the number of columns.

The whole offset function basically boils down to establishing the range
(B3:E3 as when A7=Data B) to match the first time that "" appears, which
establishes the column number for the V lookup. When you expand to a whole
month change the 4 to 31.