Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: 2 Dimensional Table Lookup

1. Established Member
Join Date
14th May 2003
Posts
405

## 2 Dimensional Table Lookup

Hello all.

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

2. Super M‌oderator
Join Date
4th July 2004
Location
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. Established Member
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

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

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))

6. ## Re: 2 dimensional lookup

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

7. ## Re: 2 dimensional lookup

i may have to change one of my add-ins to replace Sumproduct with a better alternative.
thanks

8. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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. Established Member
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

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

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