How to append reference cell in VLookup for Table_Array (1 Viewer)

[alcwho]

Hi,

I got a row number, say row 315, then i try to start vlookup from that row to the end. So my formula will look like:

=vlookup(A1,$B$315:$D$500,2,0)

however, row 315 is a dynamic cell depends on the result i generate in another cell, e.g. cell B1. Cell B1 contains the exact row number (e.g 315)

I tried to incorporate B1 to the vlookup formula like =vlookup(A1,"$B$"&B1&":$D$500",2,0) or tried to concat the 'table_array', but it still doesn't work.

Can anyone help me on this? or using other functions to get the same result?

Thanks in advance.

 

[June7]

Why does range start change?

Perhaps this will help https://officetuts.net/excel/exampl...in Excel,5 Step #5: Test the VLOOKUP Formula

 

[arnelgp]

you can also try to create a Function in a Module:

Public Function fnCreateRange(ByVal startCol As String, ByVal startRow As Long, ByVal endCol As String, ByVal endRow As Long) As Range
    Set fnCreateRange = Range("$" & startCol & "$" & startRow & ":$" & endCol & "$" & endRow)
End Function

then you can call this function within VLookup:

=VLookup(A1, fnCreateRange("B", B1, "D", 500), 2, 0)

 

[alcwho]

you can also try to create a Function in a Module:

Public Function fnCreateRange(ByVal startCol As String, ByVal startRow As Long, ByVal endCol As String, ByVal endRow As Long) As Range
    Set fnCreateRange = Range("$" & startCol & "$" & startRow & ":$" & endCol & "$" & endRow)
End Function

then you can call this function within VLookup:

=VLookup(A1, fnCreateRange("B", B1, "D", 500), 2, 0)

Thanks a lot

let me try.

 

[cheekybuddha]

I think you can also just use INDIRECT() without the need for VBA:

=VLOOKUP(A1,INDIRECT("$B$" & $B$1 & ":$D$500"),2,0)

(nb untested!)

 

[Gasman]

I think you can also just use INDIRECT() without the need for VBA:

=VLOOKUP(A1,INDIRECT("$B$" & $B$1 & ":$D$500"),2,0)

(nb untested!)

That appears to work for me?
=IF(B21="","",VLOOKUP(B21,INDIRECT("Passengers!$D$"&O21 & ":$E$997"),2,FALSE))

 

[cheekybuddha]

That appears to work for me?

Thanks for testing GM, I don't have Windows open at the moment to test.

 

[Gasman]

Thanks for testing GM, I don't have Windows open at the moment to test.

I just happened to have a workbook with a VLookup in it.