Defining a dynamic table_array for vlookup

[Howard]

I have reason to believe the second formula is correct syntac for a dymamic table_array, adapted from the first.

I do not understand the U1 and V1 comments.

What would my simple formula at the bottom look like when E1:F6 becomes a dynamic table_array?

'=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
'=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&""&V1),2,FALSE)

" ...the data I am looking for wont always be in the range M6073.
The idea being that U1 and V1 would be numbers that can change so in this case
U1 would equal 60 and V1 would equal 73"

'=VLOOKUP(B4,Sheet1!E1:F6,2,0)

Thanks.
Howard

 

[Howard]

It occurs to me to add that I want to use the formula in this macro.

.Formula = "New and exciting dynamic Vlookup formula)"
.Value = .Value


Option Explicit

Sub Sh1_To_Sh2()
With Sheets("Sheet2").Range("D4" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!E1:F6,2,0)"
.Value = .Value
End With
End Sub

Howard

 

[Claus Busch]

Hi Howard,

Am Thu, 25 Apr 2013 11:13:38 -0700 (PDT) schrieb Howard:

I do not understand the U1 and V1 comments.

in U1 is the start row and in V1 is the end row.

What would my simple formula at the bottom look like when E1:F6 becomes a dynamic table_array?

'=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
'=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&""&V1),2,FALSE)

'=VLOOKUP(B4,Sheet1!E1:F6,2,0)

your start row is 1 because of E1. E.g. write in F1:
=LOOKUP(2,1/(Sheet1!F1:F65535<>""),ROW(Sheet1!F:F))
That gives you the end row in column F in sheet1.
Then you can change your formula to:
=VLOOKUP(B4,INDIRECT("Sheet1!E1:F"&F1),2,0)
or you try it with offset:
=VLOOKUP(B4,OFFSET(Sheet1!$E$1,,,COUNTA(Sheet1!$E:$E),2),2,0)


Regards
Claus Busch

 

[Claus Busch]

Hi Howard,

Am Thu, 25 Apr 2013 11:27:02 -0700 (PDT) schrieb Howard:

Sub Sh1_To_Sh2()
With Sheets("Sheet2").Range("D4" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!E1:F6,2,0)"
.Value = .Value
End With
End Sub

you can't calculate the last row in column D because column D is empty.
Calculate the last row in column C.
But you have to calculate the last row of column F in Sheet1.
Try it so:

Sub Sh1_To_Sh2()
With Sheets("Sheet2").Range("D4" & Range("C" & Rows.Count) _
.End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"
.Value = .Value
End With
End Sub


Regards
Claus Busch

 

[Claus Busch]

Hi Howard,

Am Thu, 25 Apr 2013 20:37:42 +0200 schrieb Claus Busch:

.Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"

sorry, but I forgot a $ character. Change the line above:
..Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F$" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"

Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Thu, 25 Apr 2013 20:37:42 +0200 schrieb Claus Busch:







sorry, but I forgot a $ character. Change the line above:

.Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F$" & _

Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"



Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

With this I'm getting all #N/A's on sheet 2.

Sub Sh1_To_Sh2Claus()
With Sheets("Sheet2").Range("D4" & Range("C" & Rows.Count) _
.End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F$" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"
.Value = .Value
End With
End Sub

I'm confused where I put:

=LOOKUP(2,1/(Sheet1!F1:F65535<>""),ROW(Sheet1!F:F))

Or do I even need it with this formula?

Howard

 

[Claus Busch]

Hi Howard,

Am Thu, 25 Apr 2013 12:03:32 -0700 (PDT) schrieb Howard:

With this I'm getting all #N/A's on sheet 2.

Sub Sh1_To_Sh2Claus()
With Sheets("Sheet2").Range("D4" & Range("C" & Rows.Count) _
.End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F$" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"
.Value = .Value
End With
End Sub

I have tested it and it worked well.
Delete or comment ".value =.Value" so you can see what the formulas look
like
If it doesn't work I put a workbook in SkyDrive


Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Thu, 25 Apr 2013 12:03:32 -0700 (PDT) schrieb Howard:











I have tested it and it worked well.

Delete or comment ".value =.Value" so you can see what the formulas look

like

If it doesn't work I put a workbook in SkyDrive





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

My fault!

..Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F$" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"

Should be C4 not B4

..Formula = "=VLOOKUP(C4,Sheet1!$E$1:$F$" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"

Its a go.

Thanks, Claus.

Howard

 

[Claus Busch]

Hi Howard,

Am Thu, 25 Apr 2013 12:32:53 -0700 (PDT) schrieb Howard:

.Formula = "=VLOOKUP(B4,Sheet1!$E$1:$F$" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"

Should be C4 not B4

.Formula = "=VLOOKUP(C4,Sheet1!$E$1:$F$" & _
Sheet1.Range("F" & Rows.Count).End(xlUp).Row & ",2,0)"

glad to help.
But nevertheless have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for the workbook Howard_VLOOKUP
There are two macros and two formula suggestions in it.
You have to right-click and download the file because macros are
disabled in SkyDrive


Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Thu, 25 Apr 2013 12:32:53 -0700 (PDT) schrieb Howard:








glad to help.

But nevertheless have a look:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191

for the workbook Howard_VLOOKUP

There are two macros and two formula suggestions in it.

You have to right-click and download the file because macros are

disabled in SkyDrive





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Very nice! Makes a good study sheet for me and an excellent reference.

Regards,
Howard