A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Vlookup with variable column reference



 
 
Thread Tools Display Modes
  #1  
Old March 19th, 2010, 08:05 PM posted to microsoft.public.excel.worksheet.functions
jenVBA
external usenet poster
 
Posts: 2
Default Vlookup with variable column reference

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
  #2  
Old March 19th, 2010, 09:57 PM posted to microsoft.public.excel.worksheet.functions
Paul C
external usenet poster
 
Posts: 202
Default Vlookup with variable column reference

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:A 3,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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.