Function Lookup in Excel

[Erwin123]

Will somebody advice me on how to make a similar result in M/S Acces
using the function "lookup"( vector form ) of excel ?
Specially the functionality of lookup of Excel describe below.

=========================================
If LOOKUP can't find the lookup_value, it matches the largest value i
lookup_vector that is less than or equal to lookup_value.
=========================================

Thanks ...

 

[Michel Walsh]

Hi,


A database generally does not deal with data in memory, so, not with array
neither with vector.

Assuming a table with two fields ( keyValue, FieldForTranslation ), the
second field is the one we want to retrieve, the first one is the one used
for the comparison. The following query is a possible solution.



SELECT TOP 1 FieldForTranslation
FROM myTable
WHERE keyValue >= lookup_value
ORDER BY keyValue ASC

We just keep the records where keyValue>= supplied value, order the records
left in increasing order, pick the first record, from this record, we keep
the field of interest.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I was able to locate a function that seems to be close in functionality - you'll have to research it yourself to see if it can apply to your situation.

I found it in the Access 2003 help Table of Contents/Microsoft Access Visual Basic Reference/Microsoft Access Object Model/Functions/Functions Reference/A-D/DLookup Function.

Or just search for "Dlookup".

hth