Defining a dynamic table_array for vlookup

  • Thread starter Thread starter Howard
  • Start date Start date
H

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&":P"&V1),2,FALSE)

" ...the data I am looking for wont always be in the range M60:P73.
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
 
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:D" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!E1:F6,2,0)"
.Value = .Value
End With
End Sub

Howard
 
Hi Howard,

Am Thu, 25 Apr 2013 11:13:38 -0700 (PDT) schrieb Howard:
I do not understand the U1 and V1 comments.
Click to expand...

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&":P"&V1),2,FALSE)
Click to expand...
'=VLOOKUP(B4,Sheet1!E1:F6,2,0)
Click to expand...

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
 
Hi Howard,

Am Thu, 25 Apr 2013 11:27:02 -0700 (PDT) schrieb Howard:
Sub Sh1_To_Sh2()
With Sheets("Sheet2").Range("D4:D" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!E1:F6,2,0)"
.Value = .Value
End With
End Sub
Click to expand...

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:D" & 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
 
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)"
Click to expand...

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
 
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
Click to expand...

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

Sub Sh1_To_Sh2Claus()
With Sheets("Sheet2").Range("D4:D" & 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
 
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:D" & 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
Click to expand...

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
 
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
Click to expand...

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
 
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)"
Click to expand...

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
 
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
Click to expand...

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

Regards,
Howard
 
Back
Top