Normally the equivalent function to VLookup would be DLookup if you had an exact match. Since you don't you'll need to use DMin which is a close cousin of DMax. You want to find the smallest value in your table that's larger than your target. For example, if you look up 2.33 it brings back 3 (smallest value that's larger than the target). I cover this in Access Expert 29.

Rick hi,  I am so sorry for bother you but you are my last resources. Please help me to figure out what is my mistakes. I have to tables one with number result two rows with 3 and 1.66666 I want to compare this two number with another table RiskMatrix I saw the chapter of Expert 29 where you explain the DMAX and DLookup I used but I have error in my output. See the syntax
RiskFinal: DMax([TotalRiskFinal],"RiskMatrix",[TotalRiskFinal]=[RiskValue])
What I got back is 3 and 1.6666 but as txt format.  When I try to put the Dlookup I got errors actualy I got #Error
Expr1: (DLookUp([RiskValue],[TotalRiskFinal]=DMax([TotalRiskFinal],"RiskMatrix",[TotalRiskFinal]=[RiskValue])))
Please let me send you images! I need help on this.

Oh, and you can post images using the little "Upload Images" at the top of the thread on the right side.

I just checked my course outlines, and you already have the class I cover something very similar to this in: Access Expert 29. There's a VLOOKUP replacement equivalent in Lesson 6. Watch that. Your problem with what you posted above, though, is that you can't NEST functions like that. You need to make them separate fields.

Got it, Thanks

Still having trouble.
And is DMAX because one of the results is 1.33333 and DMax bringing that number instead of 1.0 this is the DMas syntax I am using =DMax("TotalRiskFinal","RiskMatrix","TotalRiskFinal =" & [TotalRiskFinal])

TotalRiskFinal is a query that do the calculation only two fields Member ID and calculated field.  My RiskMatrix tamble has 3 col RiskValue, RiskDesc and Comments.  RiskValue only has 3 rows 0, 2 and 3 Risk Desc has also 3 rows Low, Medium and High.

What could happen?

The Dlookup is working fine but becuase DMax not workng I have no results when the number is not whole numbers.
=DLookUp("RiskDesc","RiskMatrix","RiskValue= " & DMax("TotalRiskFinal","RiskMatrix","TotalRiskFinal =" & "TotalRiskFinal"))

Let me see your data. Upload screen shots of your queries in design view and your tables in datasheet view.

I've got error when I clic the Upload link.

Seems to work OK for me. Try again. :)

Someone else asked me a question very similar to this a little while back. I'm going to make a TechHelp video related to this. Hang tight.

One thing I just caught with the formula you pasted in:

=DLookUp("RiskDesc","RiskMatrix","RiskValue= " & DMax("TotalRiskFinal","RiskMatrix","TotalRiskFinal =" & "TotalRiskFinal"))

See anything wrong with that? You've got the last TotalRiskFinal in quotes. It should be

=DLookUp("RiskDesc","RiskMatrix","RiskValue= " & DMax("TotalRiskFinal","RiskMatrix","TotalRiskFinal =" & [TotalRiskFinal]))

And it's hard for me to help you without seeing your data too.

And wait... you don't even have a field called TotalRiskFinal in your RiskMatrix. What is that all about?

The plot thickens ;p

See if this helps: VLOOKUP in Access

I saw it 1000 times! The code for the image is:
=DMax("TotalRiskFinal","RiskMatrix","TotalRiskFinal =" & [TotalRiskFinal])

There is no field called "TotalRiskFinal" in your "RiskMatrix" table.

Apology is: =DMax("TotalRisk","RiskMatrix","TotalRisk =" & [TotalRisk])
But Total Risk is not in Risk Matrix neither! But if I put Risk Value I obtain an error #Name?

Did you put a space in there?  I'm real bad about that. space = []

V/r
Juan

Space? Where?

between commas?

His spacing is fine. The problem is that he's trying to look up a field that doesn't exist in the table! Review DLOOKUP please.

I still having problem with a query that I want to obtain a label value example I have a risk matrix 1 is Low 2 is medium and 3 is high after calculation if the result is a not exact number example 2.66666 I do not have any match I used DMAX but does work.

This is correct I still need guidance on this.

Have you managed to try the VLOOKUP example Rich linked above?
Copying that to see if it works for you first before applying to your scenario?

The problem is you're trying to look up field names that don't exist in your table.

Let me show to you guys what I have.