Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

2 Dimensional Table Lookup

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 Files
    Thanks

    - J

  • #2
    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!

    Comment


    • #3
      Re: 2 dimensional lookup

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

      Thanks again
      Thanks

      - J

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            Re: 2 dimensional lookup

            You might want to read this Thread and this one Sumproduct Replacement. Calculations Slow

            Comment


            • #7
              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

              Comment


              • #8
                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
                Kris

                ExcelFox

                Comment


                • #9
                  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

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X