Vlookup - Last found entry within table
 

Hi All,

I currently have the following VLOOKUP which queries another sheet
within the workbook that contains a set of data from a query. This
works fine until recently when the data system introduced version
controls on records. Now there could be two lines for the same
reference (i.e. B11, in this example, could have reference to record
1. But record 1 could now contain a line within the Data sheet at
line 5, for version 1, and line 28, for version 2 etc...etc...).

What I'd like to be able to do is to say within the following bring
back the last row that contains the entry defined within B11, almost
to have the formula working from the bottom of the range upwards or
any other means to achieve this?

=IF(ISERROR(VLOOKUP($B11,Data!OpenRecords,
2,FALSE)),"",VLOOKUP($B11,Data!OpenRecords,2,FALSE ))

Thanks in advance, Al.

Vlookup - Last found entry within table
 

Try this,

=IF(ISERROR(LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F28)),"",LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F29))

Note this doesn't use your named range because of the way the formula works
so
E1:E28 is the left column of your named range
F1:F28 is the second column

Mike


" wrote:

Hi All,

I currently have the following VLOOKUP which queries another sheet
within the workbook that contains a set of data from a query. This
works fine until recently when the data system introduced version
controls on records. Now there could be two lines for the same
reference (i.e. B11, in this example, could have reference to record
1. But record 1 could now contain a line within the Data sheet at
line 5, for version 1, and line 28, for version 2 etc...etc...).

What I'd like to be able to do is to say within the following bring
back the last row that contains the entry defined within B11, almost
to have the formula working from the bottom of the range upwards or
any other means to achieve this?

=IF(ISERROR(VLOOKUP($B11,Data!OpenRecords,
2,FALSE)),"",VLOOKUP($B11,Data!OpenRecords,2,FALSE ))

Thanks in advance, Al.

Vlookup - Last found entry within table
 

Oops typo

=IF(ISERROR(LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F28)),"",LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F28))


"Mike H" wrote:

Try this,

=IF(ISERROR(LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F28)),"",LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F29))

Note this doesn't use your named range because of the way the formula works
so
E1:E28 is the left column of your named range
F1:F28 is the second column

Mike


" wrote:

Hi All,

I currently have the following VLOOKUP which queries another sheet
within the workbook that contains a set of data from a query. This
works fine until recently when the data system introduced version
controls on records. Now there could be two lines for the same
reference (i.e. B11, in this example, could have reference to record
1. But record 1 could now contain a line within the Data sheet at
line 5, for version 1, and line 28, for version 2 etc...etc...).

What I'd like to be able to do is to say within the following bring
back the last row that contains the entry defined within B11, almost
to have the formula working from the bottom of the range upwards or
any other means to achieve this?

=IF(ISERROR(VLOOKUP($B11,Data!OpenRecords,
2,FALSE)),"",VLOOKUP($B11,Data!OpenRecords,2,FALSE ))

Thanks in advance, Al.

Vlookup - Last found entry within table
 

Mike,
Thanks for your help - Much appreciated. Had amended the typo but
thanks for noticing.

Cheers, Al.
On Sep 30, 10:30*am, Mike H wrote:
Oops typo

=IF(ISERROR(LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F28)),"",LOOKUP(2,1/(Data!*E1:E28=B11),Data!F1:F28))



"Mike H" wrote:
Try this,

=IF(ISERROR(LOOKUP(2,1/(Data!E1:E28=B11),Data!F1:F28)),"",LOOKUP(2,1/(Data!*E1:E28=B11),Data!F1:F29))

Note this doesn't use your named range because of the way the formula works
so
E1:E28 is the left column of your named range
F1:F28 is the second column

Mike

" wrote:

Hi All,

I currently have the following VLOOKUP which queries another sheet
within the workbook that contains a set of data from a query. *This
works fine until recently when the data system introduced version
controls on records. *Now there could be two lines for the same
reference (i.e. B11, in this example, could have reference to record
1. *But record 1 could now contain a line within the Data sheet at
line 5, for version 1, and line 28, for version 2 etc...etc...).

What I'd like to be able to do is to say within the following bring
back the last row that contains the entry defined within B11, almost
to have the formula working from the bottom of the range upwards or
any other means to achieve this?

=IF(ISERROR(VLOOKUP($B11,Data!OpenRecords,
2,FALSE)),"",VLOOKUP($B11,Data!OpenRecords,2,FALSE ))

Thanks in advance, Al.- Hide quoted text -

- Show quoted text -