two dimentional refrence on 3 conditions

D

Denisbuzz

New Member
Joined
Feb 12, 2009
Messages
23
I am trying to come up with a formula that will lookup the following data and pull out for example the number of Dog planned in December

FYI, I can not change the law out of the source data because it is commun to other departments.

A B C D E
<TABLE style="WIDTH: 273pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=365 border=0><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" span=3 width=79><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 34pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 60.75pt; BACKGROUND-COLOR: #666699" width=45 height=81 rowSpan=4>Dog</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=83></TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>Oct-08</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>Nov-08</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>Dec-08</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>Plan</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">7,463.0 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">7,311.0 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">7,461.0 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>Actual/Fcst</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc">7,415.0 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc">7,340.0 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc">7,210.0 </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: silver" height=21>Variance</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">(48.0)</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">29.0 </TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">(251.0)</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 34pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 60.75pt; BACKGROUND-COLOR: #666699" width=45 height=81 rowSpan=4>Cat</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699"></TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>Oct-08</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>Nov-08</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>Dec-08</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>Plan</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">24,940.0 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">23,683.0 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">25,296.0 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>Actual/Fcst</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc">24,938.0 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc">26,068.0 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc">26,215.0 </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: silver" height=21>Variance</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">(2.0)</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">2,385.0 </TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">919.0 </TD></TR></TBODY></TABLE>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
try something like this
Excel Workbook
ABCDE
1DOG10/1/200811/1/200812/1/2008
2Plan746373117461
3Actual/Fcts741573407210
4Variance-4829-251
5
6
7LOOKUP DATALOOKUP MONTH/YEARFORMULA
8Plan12/1/20087461
9Actual/Fcts11/1/20087340
Sheet2
Excel 2007
Cell Formulas
RangeFormula
C4=+C3-C2
C8=INDEX($C$2:$E$4,MATCH(A8,$B$2:$B$4,0),MATCH(B8,$C$1:$E$1,0))
C9=INDEX($C$2:$E$4,MATCH(A9,$B$2:$B$4,0),MATCH(B9,$C$1:$E$1,0))
D4=+D3-D2
E4=+E3-E2
 
Upvote 0
That is only good for one categorie, here Dog. I need the formula to lookup if it is a Dog or a Cat, then weither it is plan or actual and also for a specific month
 
Upvote 0
assuming your headings will match between each table...
create range names for each of your tables as defined below...
Range Names:
DOG: C2:E4
CAT: C7:E9
Excel Workbook
ABCDE
1DOG10/1/200811/1/200812/1/2008
2Plan746373117461
3Actual/Fcts741573407210
4Variance-4829-251
5
6CAT10/1/200811/1/200812/1/2008
7Plan249402368325296
8Actual/Fcts249382606826215
9Variance-22385919
10
11LOOKUP TABLELOOKUP DATALOOKUP MONTH/YEARFORMULA
12DOGPlan12/1/20087461
13DOGActual/Fcts11/1/20087340
14CATPlan12/1/200825296
15CATActual/Fcts11/1/200826068
Sheet2
Excel 2007
Cell Formulas
RangeFormula
C4=+C3-C2
C9=+C8-C7
D4=+D3-D2
D9=+D8-D7
D12=INDEX(INDIRECT(A12),MATCH(B12,$B$2:$B$4,0),MATCH(C12,$C$1:$E$1,0))
D13=INDEX(INDIRECT(A13),MATCH(B13,$B$2:$B$4,0),MATCH(C13,$C$1:$E$1,0))
D14=INDEX(INDIRECT(A14),MATCH(B14,$B$2:$B$4,0),MATCH(C14,$C$1:$E$1,0))
D15=INDEX(INDIRECT(A15),MATCH(B15,$B$2:$B$4,0),MATCH(C15,$C$1:$E$1,0))
E4=+E3-E2
E9=+E8-E7
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
571
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
912
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,223,922
Messages
6,175,386
Members
452,639
Latest member
RMH2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top