convert vlookup to access

[madeline]

Hi All,

I have an Excel formula that I need to convert to Access 2007:

=IF(F8="Active",IF(AND(C8<>"None",C8<>"COM"),VLOOKUP(N8,AQ6:AV11,L8+1,FALSE ),IF(C8="COM",VLOOKUP(J8,AX7:AY11,2,),"Error")),"Null")

The vlookup looks to this:
AQ6:AV11
1 2 3 4 5
5 11 16 20 23 25
4 7 12 17 21 24
3 4 8 13 18 22
2 2 5 9 14 19
1 1 3 6 10 15
AX7:AY11
5 25
4 21
3 13
2 5
1 1

The cell references like C8,N8 are values in the first column of the vlookup fields. This is for a ratings table.

It took me a while to figure out vlookup and it's real cool, but not in Access. I know I would have to use VBA to reproduce this, but umh not brainy enough

Argh, some help please

Thanks
Madeline

 

[John Big Booty]

Have a look at the DLookup() function which is approximatley equivlaent to the VLookup function in Excel, also check other Domain aggrigation function here.

 

[John Big Booty]

...additionally there is no way to refrence data in Acces in the manner in which you would in Excel as there is no cell refrence system.