I want to use Vlookup function and AND function in a single formu.
 

We have created excel spreadsheet. In one column we have description of the
activity , 2nd column has activitiy ranges. We have to build an VLOOKUP
formula to check the description of the activity and check the activity
ranges in the 2nd column and than give us the correct value in a particular
column (Correct values are in 4th column). Request help on the same to build
a formula using VLOOKUP AND functions.

Hi Prakash,

IF the values in the DESCRIPTION column are unique and the values in the
RANGES column are also unique, try something like this:

=VLOOKUP(VLOOKUP(F1,A1:B5,2,0),B1:E5,4,0)

Were...

F1 is the lookup value of the ACTIVITY
A1:B5 is the lookup range of the ACTIVITY and DESCRIPTION
E1:E5 is the 4th column values you want to return. (4th from column B)

If the values are not unique, as stated, this will not work.

HTH
Regards,
Howard

"prakash" wrote in message
...
We have created excel spreadsheet. In one column we have description of
the
activity , 2nd column has activitiy ranges. We have to build an VLOOKUP
formula to check the description of the activity and check the activity
ranges in the 2nd column and than give us the correct value in a
particular
column (Correct values are in 4th column). Request help on the same to
build
a formula using VLOOKUP AND functions.

Pardon the typo...

"Where" not "Were", and probably not worth another post to correct.

"L. Howard Kittle" wrote in message
...
Hi Prakash,

IF the values in the DESCRIPTION column are unique and the values in the
RANGES column are also unique, try something like this:

=VLOOKUP(VLOOKUP(F1,A1:B5,2,0),B1:E5,4,0)

Were...

F1 is the lookup value of the ACTIVITY
A1:B5 is the lookup range of the ACTIVITY and DESCRIPTION
E1:E5 is the 4th column values you want to return. (4th from column B)

If the values are not unique, as stated, this will not work.

HTH
Regards,
Howard

"prakash" wrote in message
...
We have created excel spreadsheet. In one column we have description of
the
activity , 2nd column has activitiy ranges. We have to build an VLOOKUP
formula to check the description of the activity and check the activity
ranges in the 2nd column and than give us the correct value in a
particular
column (Correct values are in 4th column). Request help on the same to
build
a formula using VLOOKUP AND functions.

Hi

Thanks for your response. I am not clear whether the formula you suggested
addresses my requirment.

I am explaining the same in detail and suggest the right formula to be used.

I am creating a spreadsheet, which has the following data

1. Column A - description of the activitiy

Example XYZ
XYZ 1
XYZ 2

2. Column B - range of activity

Example XYZ 123
XYZ1 345
XYZ2 678

3. Column C - rates for the above description and range of activities are in
a seperate table

Example XYZ 123 $10
XYZ1 345 $20
XYZ2 678 $30

4. Column D -
We want to build a formula which should first check Column A, than check
column B and then pick up the rates from Column C.

example : If the user has selected XYZ as an activity and the range selected
for that activity is 123 than the rate to be populated should be $10.

Kindly tell us which formula should we use as we have lots of different
activities with different ranges and different rates.

If i have to use Vlookup what will be the formula?
























"L. Howard Kittle" wrote:

Hi Prakash,

IF the values in the DESCRIPTION column are unique and the values in the
RANGES column are also unique, try something like this:

=VLOOKUP(VLOOKUP(F1,A1:B5,2,0),B1:E5,4,0)

Were...

F1 is the lookup value of the ACTIVITY
A1:B5 is the lookup range of the ACTIVITY and DESCRIPTION
E1:E5 is the 4th column values you want to return. (4th from column B)

If the values are not unique, as stated, this will not work.

HTH
Regards,
Howard

"prakash" wrote in message
...
We have created excel spreadsheet. In one column we have description of
the
activity , 2nd column has activitiy ranges. We have to build an VLOOKUP
formula to check the description of the activity and check the activity
ranges in the 2nd column and than give us the correct value in a
particular
column (Correct values are in 4th column). Request help on the same to
build
a formula using VLOOKUP AND functions.