Hi,

I have imported a data text file into Sheet1 of worksheet......the
first column of which has Names.

Name ACW Talk Hold etc.......

In Sheet2, i have done data validation (List) on a A2 cell & get a
dropdown list of the Names using a defined name say "Agent".

In B2, i am trying to get their calculated AHT, the formula for which
is :

AHT = ACW + Talk + Hold

I have to do 3 Vlookups to get the result.

Is there a Better way of doing this by using Vlookup ONLY ONCE & get
the same results....OR some other way ?????


Please help!

Hi,

You could try using the =OFFSET function. For this to work you will need the
cell address for the first cell. You may be able to get this with the
=MATCH, =ADDRESS and = INDIRECT functions.

This way may possibly be of some value if you have huge amounts of data and
the three lookups are taking too long.

Hope this helps.

Sean



"junoon" wrote in message
oups.com...
Hi,

I have imported a data text file into Sheet1 of worksheet......the
first column of which has Names.

Name ACW Talk Hold etc.......

In Sheet2, i have done data validation (List) on a A2 cell & get a
dropdown list of the Names using a defined name say "Agent".

In B2, i am trying to get their calculated AHT, the formula for which
is :

AHT = ACW + Talk + Hold

I have to do 3 Vlookups to get the result.

Is there a Better way of doing this by using Vlookup ONLY ONCE & get
the same results....OR some other way ?????


Please help!

yes Sean,

I have huge amount of data & it takes a lot of time.....

Any formula example as to how i would be able to accomplish this using
OFFSET or the other 3 functions???

The Headers are in the 1st row,
Names in the 1st column, ACW in 2nd column, Talk in 3rd column, Hold in
4th column....

data starts from A2 till D350.....

Rgds

Hi,

Mmm, I am not convinced that this is the best way I would have thought that
3 vlookups would have been tidier.
I have multiple vlookup's on a spreadsheet with 35,000 rows of data and have
not noticed a speed problem.

Place the name you want in cell B8
Place the sheet name (Sheet1) in B9
Place the following formula in B10
=B9&"!"&ADDRESS(MATCH(B8,Sheet1!A2:A350,0)+1,1)
This calculates the cell address of the name in B8
Place the following formula in B11
=OFFSET(INDIRECT(B10),0,1)+OFFSET(INDIRECT(B10),0, 2)+OFFSET(INDIRECT(B10),0,3)
This sums the three values in columns B, C & D

If you do not want any helper cells you can replace all the B10's in the
above with the formula for the cell address and hard code the references to
the sheet name in the formula:
=OFFSET(INDIRECT("Sheet1!"&ADDRESS(MATCH(B8,Sheet1 !A2:A350,0)+1,1)),0,1)+OFFSET(INDIRECT("Sheet1!"&A DDRESS(MATCH(B8,Sheet1!A2:A350,0)+1,1)),0,2)+OFFSE T(INDIRECT("Sheet1!"&ADDRESS(MATCH(B8,Sheet1!A2:A3 50,0)+1,1)),0,3)

If you have more than 350 data points you will need to increase the range.

If the 1st data point is not in row 2 you will need to add more than 1 to
the 1st argument in the ADDRESS function.

Perhaps you can consider summing all entries on the data sheet and doing 1
vlookup to find the 1 particular answer you are looking for.

Hope this helps.

Sean

"junoon" wrote in message
oups.com...
yes Sean,

I have huge amount of data & it takes a lot of time.....

Any formula example as to how i would be able to accomplish this using
OFFSET or the other 3 functions???

The Headers are in the 1st row,
Names in the 1st column, ACW in 2nd column, Talk in 3rd column, Hold in
4th column....

data starts from A2 till D350.....

Rgds

Junoon,

Please let us know if it is any quicker.

Sean

"Sean" wrote in message
...
Hi,

Mmm, I am not convinced that this is the best way I would have thought
that 3 vlookups would have been tidier.
I have multiple vlookup's on a spreadsheet with 35,000 rows of data and
have not noticed a speed problem.

Place the name you want in cell B8
Place the sheet name (Sheet1) in B9
Place the following formula in B10
=B9&"!"&ADDRESS(MATCH(B8,Sheet1!A2:A350,0)+1,1)
This calculates the cell address of the name in B8
Place the following formula in B11
=OFFSET(INDIRECT(B10),0,1)+OFFSET(INDIRECT(B10),0, 2)+OFFSET(INDIRECT(B10),0,3)
This sums the three values in columns B, C & D

If you do not want any helper cells you can replace all the B10's in the
above with the formula for the cell address and hard code the references
to the sheet name in the formula:
=OFFSET(INDIRECT("Sheet1!"&ADDRESS(MATCH(B8,Sheet1 !A2:A350,0)+1,1)),0,1)+OFFSET(INDIRECT("Sheet1!"&A DDRESS(MATCH(B8,Sheet1!A2:A350,0)+1,1)),0,2)+OFFSE T(INDIRECT("Sheet1!"&ADDRESS(MATCH(B8,Sheet1!A2:A3 50,0)+1,1)),0,3)

If you have more than 350 data points you will need to increase the range.

If the 1st data point is not in row 2 you will need to add more than 1 to
the 1st argument in the ADDRESS function.

Perhaps you can consider summing all entries on the data sheet and doing 1
vlookup to find the 1 particular answer you are looking for.

Hope this helps.

Sean

"junoon" wrote in message
oups.com...
yes Sean,

I have huge amount of data & it takes a lot of time.....

Any formula example as to how i would be able to accomplish this using
OFFSET or the other 3 functions???

The Headers are in the 1st row,
Names in the 1st column, ACW in 2nd column, Talk in 3rd column, Hold in
4th column....

data starts from A2 till D350.....

Rgds

You could try this *array* formula:

=SUM(VLOOKUP(A2,Sheet1!$A$2:$D$350,{2,3,4},0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You must *also* use CSE when revising the formula.

Copy down as needed *after* entering with CSE.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"junoon" wrote in message
oups.com...
Hi,

I have imported a data text file into Sheet1 of worksheet......the
first column of which has Names.

Name ACW Talk Hold etc.......

In Sheet2, i have done data validation (List) on a A2 cell & get a
dropdown list of the Names using a defined name say "Agent".

In B2, i am trying to get their calculated AHT, the formula for which
is :

AHT = ACW + Talk + Hold

I have to do 3 Vlookups to get the result.

Is there a Better way of doing this by using Vlookup ONLY ONCE & get
the same results....OR some other way ?????


Please help!

Just realized that you imported the data.
On the chance that your numbers might be recognized as text by XL, the Sum()
function will need this conversion:

=SUM(--VLOOKUP(A2,Sheet1!$A$2:$D$350,{2,3,4},0))

*STILL* an array formula needing CSE!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ragdyer" wrote in message
...
You could try this *array* formula:

=SUM(VLOOKUP(A2,Sheet1!$A$2:$D$350,{2,3,4},0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You must *also* use CSE when revising the formula.

Copy down as needed *after* entering with CSE.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"junoon" wrote in message
oups.com...
Hi,

I have imported a data text file into Sheet1 of worksheet......the
first column of which has Names.

Name ACW Talk Hold etc.......

In Sheet2, i have done data validation (List) on a A2 cell & get a
dropdown list of the Names using a defined name say "Agent".

In B2, i am trying to get their calculated AHT, the formula for which
is :

AHT = ACW + Talk + Hold

I have to do 3 Vlookups to get the result.

Is there a Better way of doing this by using Vlookup ONLY ONCE & get
the same results....OR some other way ?????


Please help!

thanks both of you.

Ragdyer, your Array formula looks short & sweet, but i understand that
i have to drag it down to fill other cells, which i am not doing....
i just want the results in one cell.

See, in cell A2 of Sheet2, i have made a dropdown list (validation
list), which i can use to show the names, but in B2, i need the
formula.
B3, B4... will not be filled....

Its like a Main Display Sheet2, which will show you all the results
from the Raw Data Sheet1....

Rgds,

Gotcha....

this works fine for me....found the solution to my problem....

{=SUM(VLOOKUP(B1,Database,{5,6,8},0))/INDEX(Database,MATCH(B1,Login,0),3)}

Where Database is a the complete data (dynamic range):
Database=OFFSET(Sheet1!$A$2,-1,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))


&
Login is the first column Agent Column (dynamic range):
Login =OFFSET(Sheet1!$A$2,-1,0,COUNTA(Sheet1!$A:$A),1)

Thanks for your input friends. kept me looking out for a short & sweet
formula......

Cheers!

Appreciate the feed-back.
Been a little too busy the past week to check the NG's.
Glad you found your own solution.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"junoon" wrote in message
oups.com...
Gotcha....

this works fine for me....found the solution to my problem....

{=SUM(VLOOKUP(B1,Database,{5,6,8},0))/INDEX(Database,MATCH(B1,Login,0),3)}

Where Database is a the complete data (dynamic range):

Database=OFFSET(Sheet1!$A$2,-1,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))


&
Login is the first column Agent Column (dynamic range):
Login =OFFSET(Sheet1!$A$2,-1,0,COUNTA(Sheet1!$A:$A),1)

Thanks for your input friends. kept me looking out for a short & sweet
formula......

Cheers!