Index Match in Named Ranges

J

JonesyUK

Board Regular
Joined
Oct 11, 2005
Messages
190
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Need some formula help please:

1) I have 12 "cubes"/ranges of data (1 for each month) on a single sheet
2) Each "cube" of data is a named range, eg. "Jan_Act", "Feb_Act", "Mar_Act" etc...
3) First row of each range contains headings which are unique names (about 200 columns)
4) First column of each range contains headings which are account categories (about 300 rows)

I am trying to do a sort of Index Match formula on a separate sheet ("Lookup") which looks for the row & column combination that is specified in cell A1 ("unique name") and cell B1 ("account category") and cell C1 ("month")

Example:
Lookup Sheet
A1 = Name20
B1 = Fuel Costs
C1 = Feb_Act

Need a formula to return the number that meets those criteria, i.e. look in cube called "Feb_Act", find "Name20" in first row of range and find "Fuel Costs" in first column of range

Hope this makes sense, and thank you!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
you sort of changed your terms through the post

so we are doing a GRID lookup based on the A1 (heading in column A rows) and B1 (Heading in Row 1 various columns
=INDEX(DataRange, MATCH(TheRows,ROW_List,0), MATCH(TheColumns,Columnlist,0))
=INDEX(Range to return value from, MATCH(A1, A2:A10000, 0), MATCH(B1, column range B1:Z1, 0))

And C1 is the Sheet to lookup
so INDIRECT(C1) to get the sheet name

As we dont have a sampole sheet loaded as XL2BB - see signature / menu or on a share - is the above correct, before i work out the rest
 
Last edited:
Upvote 0
Hi - thanks for your response, here's a snapshot example, I've put everything on one page to illustrate: so I need a formula that will give the the "answer" to the criteria...

Excel Formula.xlsx
BCDEFGHIJ
2Jan_ActJohnPaulRingoGeorgeCriteria #1Criteria #2
3Salaries9,3855,8578,4173,473Feb_ActJan_Act
4Contract staff6,9526,4107,0853,536GeorgeJohn
5Maternity Pay3,2614,6455,1005,959Maternity PayClass 1A NIC
6Sick Pay7,3933,1258,1044,371
7Overtime3,3423,6591,739954AnswerAnswer
8PILON Taxable1,2948,6148,0265075,1005,934
9National Insurance1,9802,1954021,237
10Class 1A NIC5,9347,4563,5125,889
11Non Pensionable Bonus2,5995,0317,0477,726
12Pensionable Bonus5531008621,181
13Pension Fund Contribution6,6159,8814,9348,678
14AM Quarterly Bonus Bonus5,2852,0669,1042,911
15
16Feb_ActPaulGingerGeorgeFred
17Salaries9,3855,8578,4173,473
18Contract staff6,9526,4107,0853,536
19Maternity Pay3,2614,6455,1005,959
20Sick Pay7,3933,1258,1044,371
21Overtime3,3423,6591,739954
22PILON Taxable1,2948,6148,026507
23National Insurance1,9802,1954021,237
24Class 1A NIC5,9347,4563,5125,889
25Non Pensionable Bonus2,5995,0317,0477,726
26Pensionable Bonus5531008621,181
27Pension Fund Contribution6,6159,8814,9348,678
28AM Quarterly Bonus Bonus5,2852,0669,1042,911
Sheet1
 
Upvote 0
so heres a sample with the GRID lookup and table

I was working on as you posted - with different sheets
I'll look at yours and see of i can split into seperate sheets and post back XL2BB & a share like dropbox

heres the lookup return results
Grid Lookup with Sheetname.xlsx
ABC
1Name20
2Fuel Costs12
3Mar_Act
Lookup
Cell Formulas
RangeFormula
C2C2=INDEX(INDIRECT("'"&A3&"'!$B$2:$E$11"),MATCH(A2,INDIRECT("'"&A3&"'!$A$2:$A$11"),0),MATCH($A1,INDIRECT("'"&A3&"'!$B$1:$E$1"),0))


Grid Lookup with Sheetname.xlsx
ABCDE
1Name1Name2Name20Name3
2A-0.88-0.96-1.07-1.06
3B-0.43-0.51-0.72-0.71
4C0.02-0.05-0.37-0.35
5Fuel Costs0.470.4-0.30.01
6D0.920.850.320.36
7E1.371.310.670.72
8F1.821.761.011.08
9G2.272.221.361.44
10H2.722.671.711.79
11I3.173.132.062.15
Feb_Act


Grid Lookup with Sheetname.xlsx
ABCDE
1Name1Name2Name20Name3
2A-0.88-0.96-1.07-1.06
3B-0.43-0.51-0.72-0.71
4C0.02-0.05-0.37-0.35
5Fuel Costs0.470.4120.01
6D0.920.850.320.36
7E1.371.310.670.72
8F1.821.761.011.08
9G2.272.221.361.44
10H2.722.671.711.79
11I3.173.132.062.15
Mar_Act


 
Upvote 0
here we go

=INDEX(INDIRECT("'"&A2&"'!$B$2:$E$13"),MATCH(A4,INDIRECT("'"&A2&"'!$A$2:$A$13"),0),MATCH($A3,INDIRECT("'"&A2&"'!$B$1:$E$1"),0))

NOTE the range WILL NOT copy as its hardcody in "" as part of the INDIRECT Function

Grid Lookup with Sheetname.xlsx
ABC
1Criteria #1Criteria #2
2Feb_ActJan_Act
3GeorgeJohn
4Maternity PayClass 1A NIC
5
6AnswerAnswer
75099.9406125934.293182
8
9
10
11AnswerAnswer
1251005934.293182
13wrong in sample
Result
Cell Formulas
RangeFormula
A7A7=INDEX(INDIRECT("'"&A2&"'!$B$2:$E$13"),MATCH(A4,INDIRECT("'"&A2&"'!$A$2:$A$13"),0),MATCH($A3,INDIRECT("'"&A2&"'!$B$1:$E$1"),0))
C7C7=INDEX(INDIRECT("'"&C2&"'!$B$2:$E$13"),MATCH(C4,INDIRECT("'"&C2&"'!$A$2:$A$13"),0),MATCH($C3,INDIRECT("'"&C2&"'!$B$1:$E$1"),0))


Grid Lookup with Sheetname.xlsx
ABCDE
1Feb_ActPaulGingerGeorgeFred
2Salaries9384.9815785856.5448368417.2057373472.793006
3Contract staff6952.2357686410.1770227085.4689283536.18081
4Maternity Pay3261.1883014644.7758765099.9406125958.538441
5Sick Pay7392.9771193125.0918828103.6023994371.041954
6Overtime3341.5412423658.7771761738.748973954.4748599
7PILON Taxable1293.6987628614.3410588025.977172506.8466711
8National Insurance1980.3751062195.475899401.50736161236.952693
9Class 1A NIC5934.2931827456.326423512.3202655889.1185
10Non Pensionable Bonus2598.9318715030.7295927047.1835227725.594302
11Pensionable Bonus552.615992399.51577669862.26767151180.868508
12Pension Fund Contribution6615.2340939880.6749154933.8622618678.080785
13AM Quarterly Bonus Bonus5285.0609352065.557069103.9599832911.190028
Feb_Act


Grid Lookup with Sheetname.xlsx
ABCDE
1Jan_ActJohnPaulRingoGeorge
2Salaries9384.9815785856.5448368417.2057373472.793006
3Contract staff6952.2357686410.1770227085.4689283536.18081
4Maternity Pay3261.1883014644.7758765099.9406125958.538441
5Sick Pay7392.9771193125.0918828103.6023994371.041954
6Overtime3341.5412423658.7771761738.748973954.4748599
7PILON Taxable1293.6987628614.3410588025.977172506.8466711
8National Insurance1980.3751062195.475899401.50736161236.952693
9Class 1A NIC5934.2931827456.326423512.3202655889.1185
10Non Pensionable Bonus2598.9318715030.7295927047.1835227725.594302
11Pensionable Bonus552.615992399.51577669862.26767151180.868508
12Pension Fund Contribution6615.2340939880.6749154933.8622618678.080785
13AM Quarterly Bonus Bonus5285.0609352065.557069103.9599832911.190028
Jan_Act


share - only for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
 
Upvote 0
etaf said:
you sort of changed your terms through the post

so we are doing a GRID lookup based on the A1 (heading in column A rows) and B1 (Heading in Row 1 various columns
=INDEX(DataRange, MATCH(TheRows,ROW_List,0), MATCH(TheColumns,Columnlist,0))
=INDEX(Range to return value from, MATCH(A1, A2:A10000, 0), MATCH(B1, column range B1:Z1, 0))

And C1 is the Sheet to lookup
so INDIRECT(C1) to get the sheet name

As we dont have a sampole sheet loaded as XL2BB - see signature / menu or on a share - is the above correct, before i work out the rest
Click to expand...
thank you, here's a snapshot of the spreadsheet...

Excel Formula.xlsx
BCDEFGHIJ
2Jan_ActJohnPaulRingoGeorgeCriteria #1Criteria #2
3Salaries9,3855,8578,4173,473Feb_ActJan_Act
4Contract staff6,9526,4107,0853,536GeorgeJohn
5Maternity Pay3,2614,6455,1005,959Maternity PayClass 1A NIC
6Sick Pay7,3933,1258,1044,371
7Overtime3,3423,6591,739954AnswerAnswer
8PILON Taxable1,2948,6148,0265075,1005,934
9National Insurance1,9802,1954021,237
10Class 1A NIC5,9347,4563,5125,889
11Non Pensionable Bonus2,5995,0317,0477,726
12Pensionable Bonus5531008621,181
13Pension Fund Contribution6,6159,8814,9348,678
14AM Quarterly Bonus Bonus5,2852,0669,1042,911
15
16Feb_ActPaulGingerGeorgeFred
17Salaries9,3855,8578,4173,473
18Contract staff6,9526,4107,0853,536
19Maternity Pay3,2614,6455,1005,959
20Sick Pay7,3933,1258,1044,371
21Overtime3,3423,6591,739954
22PILON Taxable1,2948,6148,026507
23National Insurance1,9802,1954021,237
24Class 1A NIC5,9347,4563,5125,889
25Non Pensionable Bonus2,5995,0317,0477,726
26Pensionable Bonus5531008621,181
27Pension Fund Contribution6,6159,8814,9348,678
28AM Quarterly Bonus Bonus5,2852,0669,1042,911
Sheet1
 
Upvote 0
you posted in previous post , we probably crossed post at same time
i have split up into different sheets added as XL2BB and offered a solution , also on a drob box share

- see my previous posts
 
Upvote 0
With your current setup, how about
Excel Formula: 
=LET(Tbl,INDIRECT(H3),INDEX(Tbl,MATCH(H5,INDEX(Tbl,,1),0),MATCH(H4,INDEX(Tbl,1,),0)))
 
Upvote 0
Thanks, the difficulty I have is that the 12 grids of data are all on ONE Sheet, so 12 cubes of data on one sheet, so I need one formula that can go to the right grid and pull the info... sorry if I'm not being too clear.
 
Upvote 0
Fluff said:
With your current setup, how about
Excel Formula: 
=LET(Tbl,INDIRECT(H3),INDEX(Tbl,MATCH(H5,INDEX(Tbl,,1),0),MATCH(H4,INDEX(Tbl,1,),0)))
Click to expand...
Oh wow, awesome, this works! Thanks so much! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Sum data in a range based on a column and row and an equation
Replies
1
Views
121
bobaol
B
szita2000
  • Question
Index Match Match from a named table to a range
Replies
1
Views
299
Alex Blakenburg
A
D
  • Solved
Populating multiple sheet names using Index, Match and Indirect
Replies
7
Views
152
Fluff
Fluff
N
  • Question
Index, Indirect, Match query
Replies
1
Views
60
Fluff
Fluff
T
  • Question
Lookup within a lookup
2
Replies
10
Views
221
TimeForTim
T

Forum statistics

Threads
1,214,797
Messages
6,121,629
Members
449,041
Latest member
Postman24

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