Need help with vlookup

N

Nash

Allergy 4
Cardiovascular 3
Anesthesiology 2
Allergy 2
Cardiovascular 1
Anesthesiology 3
Allergy 1

How do I retrieve the value of "Allergy" & Year=4 of the above table from
the following table using vlookup function and making that as an automated
feature?


v-lookup table

SPECIALTY 1 2 3 4

Allergy 6,409 9,601 10,565 11,203
Anesthesiology 6,124 10,488 15,860 17,290
Cardiovascular 32,157 55,073 83,279 90,790

Thank you.
 
P

pietlinden

Allergy                 4
Cardiovascular  3
Anesthesiology  2
Allergy                 2
Cardiovascular  1
Anesthesiology  3
Allergy                 1

How do I retrieve the value of "Allergy" & Year=4 of the above table from
the following table using vlookup function and making that as an automated
feature?

v-lookup table

SPECIALTY               1       2       3       4

Allergy         6,409   9,601   10,565  11,203
Anesthesiology  6,124   10,488  15,860  17,290  
Cardiovascular  32,157  55,073  83,279  90,790

Thank you.
Click to expand...

wrong NG. VLOOKUP is an Excel function.
 
Y

Yong Heng

Hi,

A B C
1 Allergy 4 =vlookup(A1, $A$100:$E$103,B1+1,False)
2 Cardiovascular 3 =vlookup(A2, $A$100:$E$103,B2+1,False)
3 Anesthesiology 2 ...
4 Allergy 2 ...
5 Cardiovascular 1
6 Anesthesiology 3
7 Allergy 1

....
A B C D E
100 SPECIALTY 1 2 3 4
101 Allergy 6,409 9,601 10,565 11,203
102 Anesthesiology 6,124 10,488 15,860 17,290
103 Cardiovascular 32,157 55,073 83,279 90,790

if u understand vlookup, u will note that I am taking the simple way out of
determining the relative position of the data to be returned.

I hope this helps. Let me know if you have further queries.
 
J

John W. Vinson

Allergy 4
Cardiovascular 3
Anesthesiology 2
Allergy 2
Cardiovascular 1
Anesthesiology 3
Allergy 1

How do I retrieve the value of "Allergy" & Year=4 of the above table from
the following table using vlookup function and making that as an automated
feature?


v-lookup table

SPECIALTY 1 2 3 4

Allergy 6,409 9,601 10,565 11,203
Anesthesiology 6,124 10,488 15,860 17,290
Cardiovascular 32,157 55,073 83,279 90,790

Thank you.
Click to expand...

Well, you would not use the Excel VLookUp function since it does not even
*exist* in Access. It's a function in Excel.

Instead it would appear that what you want is a Crosstab query. You chose not
to post your fieldnames nor any indication where the numbers in the grid might
come from, so I can't be specific, but you'ld use the first column
(SPECIALTY?) as the query's Row Header and the second column as the Column
Header in the crosstab.
 
N

Nash

Tahnk you Yong for helping me on this. No need to apologise. Your help is
greatly appreciated.
 
N

Nash

John,

Thank you for trying to help me. I am probably making it more complicated
than it needs to be.

I will have one table like what I have shown below with 5 columns with
specialty being as pk.


SPECIALTY 1 2 3 4

Allergy 6,409 9,601 10,565 11,203
Anesthesiology 6,124 10,488 15,860 17,290
Cardiovascular 32,157 55,073 83,279 90,790


Now the user will get a list of "specialty" as a dropdown in a form view. If
the user choose "specialty" from the drop down menu (i.e., Allergy) and enter
the year (= 4)in a text box, then a query will look for the value of
"Allergy" & "4" from the table and automatically store that value (11,203) in
a field called "price" in a separate table.

So, I guess I need help with building a query that will search multiple
columns and will extract the value at junction. Hope this is making sense.
Thanks.
 
J

John W. Vinson

John,

Thank you for trying to help me. I am probably making it more complicated
than it needs to be.

I will have one table like what I have shown below with 5 columns with
specialty being as pk.


SPECIALTY 1 2 3 4

Allergy 6,409 9,601 10,565 11,203
Anesthesiology 6,124 10,488 15,860 17,290
Cardiovascular 32,157 55,073 83,279 90,790


Now the user will get a list of "specialty" as a dropdown in a form view. If
the user choose "specialty" from the drop down menu (i.e., Allergy) and enter
the year (= 4)in a text box, then a query will look for the value of
"Allergy" & "4" from the table and automatically store that value (11,203) in
a field called "price" in a separate table.

So, I guess I need help with building a query that will search multiple
columns and will extract the value at junction. Hope this is making sense.
Thanks.
Click to expand...

Again:

You're on the wrong track.
You do not need another table (in fact your new proposed table would need to
be redesigned and restructured every year).
You do not need *ANY* sort of lookups.
You do not need any code.

You need a Crosstab Query, with the SPECIALTY as the Row Header and the year
as the Column Header.

Did you even look at the help for Crosstab?
 
Top