Prev: Vlookup with If condition
Next: Posting the workbook
From: jenVBA on 19 Mar 2010 15:05
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
From: Paul C on 19 Mar 2010 16:57
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.

--
If this helps, please remember to click yes.


"jenVBA" wrote:

> 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
 | 
Pages: 1
Prev: Vlookup with If condition
Next: Posting the workbook