Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: lookup simultaneous 3 columns

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    how can we look up values by lookup function if the data apoints are alongside in 3 diff columns- one after the other. Can we look up a no. of ids against a series of dates in the adjacent columns and their performance metrics in teh following columns.. an advanced for of step by step column filtering ?

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It is certainly possible. Depending on your data, either SUMPRODUCT, INDEX/MATCH, The Deatabase functions, or VBA seem to be likely candidates.

    Please provide more details to get a specific response.

    Bye,
    Jay

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help.

    I am facing an issue with the file below.
    I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
    Pls. advise and help wit this…
    calls contacts
    4/25/02 GCVBMVT 10554 9497
    4/25/02 GCVBSFT 7471 6483
    4/25/02 GCVBMVT 10554 9497
    4/24/02 GCVBIUT 607 537
    4/24/02 GCVBIPT 11993 10859
    4/24/02 GCVBKGT 5873 5085
    4/24/02 GCVBIZT 10865 9958
    4/23/02 GCVBIUT 607 537
    4/23/02 GCVBIPT 11993 10859
    4/23/02 GCVBKGT 5873 5085
    4/23/02 GCVBIZT 10865 9958
    4/23/02 GCVGSBT 4749 4255
    4/23/02 GCVGVFT 9350 8428
    4/23/02 GCVBIKT 8135 7319
    4/23/02 GCVBPET 7954 7157
    4/23/02 GCVBMZT 10426 9353
    4/23/02 GCVBMWT 5935 5270
    4/23/02 GCVBPAT 9740 8723
    4/23/02 GCVBMVT 10554 9497

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-27 06:22, saurabh_sharma wrote:
    Thanks for the help.

    I am facing an issue with the file below.
    I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
    Pls. advise and help wit this…
    calls contacts
    4/25/02 GCVBMVT 10554 9497
    4/25/02 GCVBSFT 7471 6483
    4/25/02 GCVBMVT 10554 9497
    4/24/02 GCVBIUT 607 537
    4/24/02 GCVBIPT 11993 10859
    4/24/02 GCVBKGT 5873 5085
    4/24/02 GCVBIZT 10865 9958
    4/23/02 GCVBIUT 607 537
    4/23/02 GCVBIPT 11993 10859
    4/23/02 GCVBKGT 5873 5085
    4/23/02 GCVBIZT 10865 9958
    4/23/02 GCVGSBT 4749 4255
    4/23/02 GCVGVFT 9350 8428
    4/23/02 GCVBIKT 8135 7319
    4/23/02 GCVBPET 7954 7157
    4/23/02 GCVBMZT 10426 9353
    4/23/02 GCVBMWT 5935 5270
    4/23/02 GCVBPAT 9740 8723
    4/23/02 GCVBMVT 10554 9497
    If you're going to create a pivot table from your data, you better use a dynamic name range and give this name as Range to the Wizard.

    I'll assume the sample above to be in A1:D20 including labels/column headings in a worksheet called Data.

    Activate Insert|Name|Define.
    Enter Nrecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Data!$A:$A)

    Note that this formula uses your dates column.

    Activate Add. (Don't leave yet the Define Name window.)

    Enter Drange as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$A$1,0,0,Nrecs,4)

    Note that 4 indicates how many columns your data area covers.

    Activate OK.

    Now give Drange as value of Range in PivotTable Wizard.

    The above allows you to add to or delete from your data area as many records as you wish. At refresh PivotTable will know the new range.

    Aladin




  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for teh help wit pivot table
    can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

    On 2002-04-27 06:52, Aladin Akyurek wrote:
    On 2002-04-27 06:22, saurabh_sharma wrote:
    Thanks for the help.

    I am facing an issue with the file below.
    I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
    Pls. advise and help wit this…
    calls contacts
    4/25/02 GCVBMVT 10554 9497
    4/25/02 GCVBSFT 7471 6483
    4/25/02 GCVBMVT 10554 9497
    4/24/02 GCVBIUT 607 537
    4/24/02 GCVBIPT 11993 10859
    4/24/02 GCVBKGT 5873 5085
    4/24/02 GCVBIZT 10865 9958
    4/23/02 GCVBIUT 607 537
    4/23/02 GCVBIPT 11993 10859
    4/23/02 GCVBKGT 5873 5085
    4/23/02 GCVBIZT 10865 9958
    4/23/02 GCVGSBT 4749 4255
    4/23/02 GCVGVFT 9350 8428
    4/23/02 GCVBIKT 8135 7319
    4/23/02 GCVBPET 7954 7157
    4/23/02 GCVBMZT 10426 9353
    4/23/02 GCVBMWT 5935 5270
    4/23/02 GCVBPAT 9740 8723
    4/23/02 GCVBMVT 10554 9497
    If you're going to create a pivot table from your data, you better use a dynamic name range and give this name as Range to the Wizard.

    I'll assume the sample above to be in A1:D20 including labels/column headings in a worksheet called Data.

    Activate Insert|Name|Define.
    Enter Nrecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Data!$A:$A)

    Note that this formula uses your dates column.

    Activate Add. (Don't leave yet the Define Name window.)

    Enter Drange as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$A$1,0,0,Nrecs,4)

    Note that 4 indicates how many columns your data area covers.

    Activate OK.

    Now give Drange as value of Range in PivotTable Wizard.

    The above allows you to add to or delete from your data area as many records as you wish. At refresh PivotTable will know the new range.

    Aladin




  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default


    On 2002-04-27 07:37, saurabh_sharma wrote:
    Thanks for teh help wit pivot table
    can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?


    You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Saurabh:
    Thanks for teh help wit pivot table
    can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?
    It is not quite clear what exactly you are asking, you do not however necessarily need a VLOOKUP function. Let us look into your data a little bit ... if the first record started in cell A2, then

    A2 will house 4/25/2002
    B2 will house GCVBMVT
    C2 will house 10554
    D2 will house 9497

    so your entire dataset will be housed in cells A2:D20

    Now let us say you are interested in locating a record with ID of GCVBIUT (say in cell F2)... you can locate in which row it lies by using the following formula in cell G2 ...

    =MATCH($F2,B2:B20,0) will give 4 as the row number where this recod is encountered first

    then
    =INDEX($A$2:$D$20,MATCH($F2,$B$2:$B$20,0),1) will give you 4/24/2002 as the date associated with this record the first time this record is encountered

    so you can also locate the other two pareameters associated with this record by adjusting the above formula

    if you want to count the number of times this ID is encountered in your dataset, use the formula ...

    =COUNTIF($B$2:$B$20,$F2) ... to give 2

    I believe the formulas that you will need are covered in the set of formulas delineated above. I hope this will help you in your project of charting the data and the associated parameters.

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!
    _________________
    Yogi Anand

    Edit: Deleted inactive website from hardcoded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 13:35 ]

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for seeking more info.
    I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
    Hope it clarifies...
    On 2002-04-27 08:19, Aladin Akyurek wrote:

    On 2002-04-27 07:37, saurabh_sharma wrote:
    Thanks for teh help wit pivot table
    can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?


    You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin:
    I did not see your last post, before I posted mine -- as you stated, it is not clear what specifically Saurabh is asking.

    Regards!

    Yogi Anand


  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for seeking more info.
    I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
    Hope it clarifies...



    On 2002-04-27 08:33, Yogi Anand wrote:
    Hi Aladin:
    I did not see your last post, before I posted mine -- as you stated, it is not clear what specifically Saurabh is asking.

    Regards!

    Yogi Anand


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •