Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Beyond a vlookup (97/2000)

    Vlookups are great, but what we need is a cross referenced table. Where you look both horizontal and vertical to find your matching value. Is there an Excel function to accomplish this?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beyond a vlookup (97/2000)

    Take a look at this Chip Pearson page. Look for the heading

    Double Lookups

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Beyond a vlookup (97/2000)

    You can do this In Excel.
    First, from the top panel command bar use Tools -> Options -> Calculation and then check the checkbox in the bottom right corner that says [Accept labels in formulas]
    Now, in a blank sheet anywhere, create a 2-dimensional block with headings in the first row representing say Depts,
    e.g. Admin, Sales, Office, Transport, DeptD, DeptE etc.
    In the left-hand first column of the table place your other list
    e.g. staff, postage, phones, wages, gas, electric, cars etc etc.
    Fill in some numbers in the table.
    Now, in any cell type a formula like
    =wages Sales
    and it will return the matching entry!
    Other examples:
    =(Admin gas)*2
    =(staff Office)+(staff Admin) + (DeptD staff)

    try it!

    zeddy

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Beyond a vlookup (97/2000)

    Zeddy, thanks for the tip... now, to make it a little more useful, how would you reference the table from another sheet in the workbook. I/m thinking it would be useful to have a worksheet with several tables and the calculations done on the first page (worksheet).

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Beyond a vlookup (97/2000)

    Without a clear picture of what you are trying to do, it's hard to tell, but there's also = INDEX() if it fits what you want.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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