Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: 2 Dimensional Table Lookup

  1. #1
    Join Date
    14th May 2003
    Posts
    405

    2 Dimensional Table Lookup

    Hello all.

    Can someone please help me here.

    As you can see in the attached image, I have a 2-Dimensional table.
    When a users selects a given Priority (High, Medium, or Low) and a Frequency (Routine, Periodic, or Occasional), I want the appropriate result to be returned e.g.

    If user selects HIGH as priority and Periodic as Frequency, then he should get 11 .00 as the answer.

    Will appreciate if someone can come to the rescue.... I think this calls for a combination of VLOOUP and HLOOKUP but this is where I get stuck

    Many thanks in advance
    Attached Images Attached Images
    Thanks

    - J

  2. #2
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,371

    Re: 2 dimensional lookup

    Assuming that A1:D4 contains the table, try...

    =INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),MATCH(G2,$B$1:$D$1,0))

    ...where F2 contains High, and G2 contains Periodic.

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th May 2003
    Posts
    405

    Re: 2 dimensional lookup

    Tons of thanks Domenic.
    Please allow me to post back after goving it a try.

    Thanks again
    Thanks

    - J

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: 2 dimensional lookup

    I would reformat your table layout and include a Priority Column where you can can add High, Low etc. Then simply use a Pivot Tables

  5. #5
    Join Date
    13th September 2003
    Location
    Singapore
    Posts
    1,236

    Re: 2 dimensional lookup

    Sumproduct is my favorite,

    with the table in A1:D4
    and F1 = High,
    G1= Periodic

    use:

    =SUMPRODUCT((A2:A4=F1)*(B1:D1=G1)*(B2:D4))
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

  7. #7
    Join Date
    13th September 2003
    Location
    Singapore
    Posts
    1,236

    Re: 2 dimensional lookup

    very interesting read Dave,

    i may have to change one of my add-ins to replace Sumproduct with a better alternative.
    thanks
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: 2 dimensional lookup

    Or....

    =VLOOKUP(F2,A2:D4,MATCH(G2,B1:D1,0)+1,0)

    where F2 contains High, and G2 contains Periodic

    HTH

  9. #9
    Join Date
    14th May 2003
    Posts
    405

    Re: 2 dimensional lookup

    Thanks everyone. That is a lot of food for digestion
    Will try to go through and understand the logic... Till then thanks to Domenic; your suggested solution works great
    Thanks

    - J

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. 3 dimensional lookup
    By Ken MacPherson in forum Excel General
    Replies: 8
    Last Post: February 16th, 2006, 05:55
  2. table lookup
    By sginther in forum Excel General
    Replies: 4
    Last Post: July 29th, 2004, 01:29
  3. Wanted: 2 Dimensional Excel Lookup formula
    By Cees in forum Excel General
    Replies: 2
    Last Post: June 15th, 2004, 00:10
  4. [Solved] Formulas: strange 2 dimensional lookup
    By Jason_Moffitt in forum Excel General
    Replies: 2
    Last Post: October 25th, 2003, 02:33

Bookmarks

Posting Permissions

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