Hey MVP's - Like VLookUp True/Closest Match - help

[Guest]

Help! I posted this two days ago and unfortunately received no response. I
did modify it slightly based on my expected lookup results - sorry if that
caused any confusion. I would like to know if what I am seeking is even
possible within Access. If it's not, please let me know.

Here's the original post (slightly modified):

I have two vlookups being done in Excel that I want to move to Access. One is
a closest match numeric and the other is a closest match alpha. For example:

The numeric:

Cell value is 3.250

Look Up Table:
1=A
2=B
4=C
8=D

I expect and receive a result of B (greater then 2 but less then 4), a cell
value of 6.75 would return a C (greater then 4 but less then 8) and so on.

Alpha:

The cell value is MH

Look Up Table:

AC=Yadda1
FG=Yadda2
NP=Yadda3
RR=Yadda4

I expect and receive Yadda2 (after FG but before NP), a cell value of OP
would result in Yadda3 (after NP but before RR) and so on.

Both are your basic True within a vlookup (closest without going over). How
can I link two tables and achieve a closest without going over match in
Access?

Thank you for your and consideration.

Regards,
Mike

 

[Allen Browne]

The DLookup() in Access is not up to scratch for this.
Copy the ELookup() function from this link:
http://allenbrowne.com/ser-42.html

Use it like this:
=ELookup("Field2", "Table1", [MyValue] & " < Field1", "Field1 DESC")

For the text example, you need extra quotes:
=ELookup("Field2", "Table1", """" & [MyValue] & """ < Field1", "Field1
DESC")

If you need something more powerful/involved there are 4 other solutions
here:
http://www.mvps.org/access/queries/qry0020.htm

 

[peregenem]

a closest match numeric

Cell value is 3.250

Look Up Table:
1=A
2=B
4=C
8=D

I expect and receive a result of B (greater then 2 but less then 4), a cell
value of 6.75 would return a C (greater then 4 but less then 8) and so on.

CREATE TABLE Test
(key_col INTEGER NOT NULL,
data_col NCHAR(1) NOT NULL);

INSERT INTO Test VALUES (1,'A');
INSERT INTO Test VALUES (2,'B');
INSERT INTO Test VALUES (4,'C');
INSERT INTO Test VALUES (8,'D');

SELECT T1.data_col
FROM Test as T1
WHERE T1.key_col = (
SELECT MAX(T2.key_col)
FROM Test AS T2
WHERE T2.key_col <= 3.250);

FWIW I'd consider the ' closest match numeric' to be C i.e.

SELECT T1.data_col
FROM Test as T1
WHERE ABS(3.520 - T1.key_col) = (
SELECT MIN(ABS(3.520 - T2.key_col))
FROM Test AS T2);

so I am glad you put sample data and expected results in your spec