Index match?? Index Offset??

IluvPivots

IluvPivots

Board Regular
Joined
Feb 13, 2007
Messages
141
Office Version
  1. 2016
In Sheet "Justification Template" I need a formula.We are returning a value from sheet "CIV Endstrength". The formula will be in sheet "Justification Template".Using the value in cell A10 of "Justification Template"...take that value and look for it in all of "CIV Endstrength" Sheet. Once excel finds it in "CIV Endstrength"...in that tab...go down 6 rows and over 3 coumns. Return that value.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
You may need to change the row and column numbers at the end of this, but this gets the mapping down, i'm just not sure what you want to have returned.
here is my sample data, and i'm only searching this data, you'll need to adjust references as you see fit. If you want 88 returned in this example you need to use 6,3, (E7)>> 88, versus the 7,4(F8)>>37 i have below.

Additionally, it only will find the first match, you will get a #REF error if there are duplicates.

Book3
ABCDEFGHIJ
1723464206089642959
230349429771489259013
317336164109679345616
446629012553089887077
58428982419960746977
62480618835489923091
730474232883131402247
880683123263792486340
9766966279010487155
102141829749791364525
CIV Endstrength


(formula edited 7 minutes after posting, I had XMATCH and then realized you do not have that function)
Book3
ABC
11Seek:Return:
129437
Justification Template
Cell Formulas
RangeFormula
C12C12=INDEX( INDEX('CIV Endstrength'!$A$1:$J10, SUM(IFERROR(MATCH('CIV Endstrength'!$A$1:$J$10,A12,0)*(ROW(A1:A10)),0)), SUM(IFERROR(MATCH('CIV Endstrength'!$A$1:$J$10,A12,0)*(COLUMN(A1:J1)),0))): 'CIV Endstrength'!$J$10, 7,4)
 
Last edited:
Upvote 0
awoohaw said:
You may need to change the row and column numbers at the end of this, but this gets the mapping down, i'm just not sure what you want to have returned.
here is my sample data, and i'm only searching this data, you'll need to adjust references as you see fit. If you want 88 returned in this example you need to use 6,3, (E7)>> 88, versus the 7,4(F8)>>37 i have below.

Additionally, it only will find the first match.

Book3
ABCDEFGHIJ
1723464206089642959
230349429771489259013
317336164109679345616
446629012553089887077
58428982419960746977
62480618835489923091
730474232883131402247
880683123263792486340
9766966279010487155
102141829749791364525
CIV Endstrength

Book3
ABC
11Seek:Return:
129437
Justification Template
Cell Formulas
RangeFormula
C12C12=INDEX( INDEX('CIV Endstrength'!$A$1:$J10, SUM(IFERROR(MATCH(' CIV Endstrength'!$A$1:$J$10,A12,0)*(ROW(A1:A10)),0)), SUM(IFERROR(MATCH('CIV Endstrength'!$A$1:$J$10,A12,0)*(COLUMN(A1:J1)),0))):'CIV Endstrength'!$J$10, 7,4)
Click to expand...
Thank you! I think this was close to what I need, but I'm using Excel 2016 at work and I don't think it recognizes XMATCH.
What I'm ultimately doing is collecting various workbooks with this specific table, however, the table itself will not start on the same row for each workbook. So I'm looking for Sep 2024 header (on whatever row that may be) and then down 6 over 3 to get to their total onboard #. The cell with the value does not have a header, or else I would do an hlookup! I hope that makes sense.
 
Upvote 0
I edited the formula in the post. and made some conditional statements after I submitted.
I think it works for you now.
 
Upvote 0
Got an #N/A. I apologize for the strain on the eyes. I'm attaching a sreenshot. The yellow cell is what I'm trying to return based on "Sep 2024"(cell AT8).
I tried downloading the L2BB app and was not successful hence the screenshot. The workbooks I get will be random for which row the header is in.
Only worried about Group A table.
2024-02-21_13-17-31.png
 
Upvote 0
it seems to work for me. Try this in a brand new workbook before inserting the functionality into yours:
(proof of concept)
Book1
ABCDE
1
2
3
4
5
6
7
8
9
10
11Seek:Return:
122023-10-011
132023-11-012
142023-12-013
152024-01-014
162024-02-015
172024-03-016
182024-04-017
192024-05-018
202024-06-019
212024-07-0110
222024-08-0111
232024-09-0112
242024-10-0113
252024-11-0114
262024-12-0115
272025-01-0116
282025-02-0117
292025-03-0118
302025-04-0119
312025-05-0120
322025-06-0121
332025-07-0122
342025-08-0123
352025-09-0124
362025-10-0125
372025-11-0126
382025-12-0127
392026-01-0128
402026-02-0129
412026-03-0130
422026-04-0131
432026-05-0132
442026-06-0133
452026-07-0134
462026-08-0135
472026-09-0136
482026-10-0137
492026-11-0138
502026-12-0139
512027-01-0140
522027-02-0141
532027-03-0142
542027-04-0143
552027-05-0144
562027-06-0145
572027-07-0146
582027-08-0147
592027-09-0148
602027-10-010
Jurisdiction Template
Cell Formulas
RangeFormula
A13:A60A13=EDATE(A12,1)
C12:C60C12=INDEX( INDEX('CIV Endstrength'!$A$1:$AW$35, SUM(IFERROR(MATCH('CIV Endstrength'!$A$1:$AW$35,A12,0)*(ROW($A$1:$A$35)),0)), SUM(IFERROR(MATCH('CIV Endstrength'!$A$1:$AW$35,A12,0)*(COLUMN($A$1:$AW$1)),0))): 'CIV Endstrength'!$AW$35, 7,4)
.




Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
1
2
3
4
5
6
7Group: A
8FY2024Oct-2023Nov-2023Dec-2023Jan-2024Feb-2024Mar-2024Apr-2024May-2024Jun-2024Jul-2024Aug-2024Sep-2024
9004210042100421004210042100421004210042100421004210042100421
10NWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotal
11Sep-23101120223033404450556066707780889099100101011011111201212
12Gain000000000000000000000000000000000000000000000000
13Loss000000000000000000000000000000000000000000000000
14EOM101120223033404450556066707780889099100101011011111201212
15Oct-2024May-1927Nov-2024May-1927Dec-2024May-1927Jan-2025May-1927Feb-2025May-1927Mar-2025May-1927Apr-2025May-1927May-2025May-1927Jun-2025May-1927Jul-2025May-1927Aug-2025May-1927Sep-2025May-1927
16004210042100421004210042100421004210042100421004210042100421
17NWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotal
18130131314014141501515160161617017171801818190191920020202102121220222223023232402424
19000000000000000000000000000000000000000000000000
20000000000000000000000000000000000000000000000000
21130131314014141501515160161617017171801818190191920020202102121220222223023232402424
22Oct-2025May-1927Nov-2025May-1927Dec-2025May-1927Jan-2026May-1927Feb-2026May-1927Mar-2026May-1927Apr-2026May-1927May-2026May-1927Jun-2026May-1927Jul-2026May-1927Aug-2026May-1927Sep-2026May-1927
23004210042100421004210042100421004210042100421004210042100421
24NWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotal
25250252526026262702727280282829029293003030310313132032323303333340343435035353603636
26000000000000000000000000000000000000000000000000
27000000000000000000000000000000000000000000000000
28250252526026262702727280282829029293003030310313132032323303333340343435035353603636
29Oct-2026May-1927Nov-2026May-1927Dec-2026May-1927Jan-2027May-1927Feb-2027May-1927Mar-2027May-1927Apr-2027May-1927May-2027May-1927Jun-2027May-1927Jul-2027May-1927Aug-2027May-1927Sep-2027May-1927
30004210042100421004210042100421004210042100421004210042100421
31NWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotalNWCFMRTFETotalTotal
32370373738038383903939400404041041414204242430434344044444504545460464647047474804848
33000000000000000000000000000000000000000000000000
34000000000000000000000000000000000000000000000000
35370373738038383903939400404041041414204242430434344044444504545460464647047474804848
CIV Endstrength
Cell Formulas
RangeFormula
D11,AV32,AR32,AN32,AJ32,AF32,AB32,X32,T32,P32,L32,H32,D32,AV25,AR25,AN25,AJ25,AF25,AB25,X25,T25,P25,L25,H25,D25,AV18,AR18,AN18,AJ18,AF18,AB18,X18,T18,P18,L18,H18,D18,AV11,AR11,AN11,AJ11,AF11,AB11,X11,T11,P11,L11,H11D11=SUM(B11:C11)
E11,AW32,AS32,AO32,AK32,AG32,AC32,Y32,U32,Q32,M32,I32,E32,AW25,AS25,AO25,AK25,AG25,AC25,Y25,U25,Q25,M25,I25,E25,AW18,AS18,AO18,AK18,AG18,AC18,Y18,U18,Q18,M18,I18,E18,AW11,AS11,AO11,AK11,AG11,AC11,Y11,U11,Q11,M11,I11E11=SUM(D11)
F11,AT32,AP32,AL32,AH32,AD32,Z32,V32,R32,N32,J32,F32,AT25,AP25,AL25,AH25,AD25,Z25,V25,R25,N25,J25,F25,AT18,AP18,AL18,AH18,AD18,Z18,V18,R18,N18,J18,F18,AT11,AP11,AL11,AH11,AD11,Z11,V11,R11,N11,J11F11=B11+1
B14:AW14,B35:AW35,B28:AW28,B21:AW21B14=SUM(B11:B13)
B18,B32,B25B18=B11+12
 
Last edited:
Upvote 0
you may need to commit the formula to the cell with the CNRL-SHFT-ENTR (CSE) keystroke. I'm not sure if array functionality is inherent n 2016.

and if you get errors, please paste the formula and what cell it is in in the thread. As well as the target cell location and the output cell location.
 
Upvote 1
Solution
awoohaw said:
you may need to commit the formula to the cell with the CNRL-SHFT-ENTR (CSE) keystroke. I'm not sure if array functionality is inherent n 2016.

and if you get errors, please paste the formula and what cell it is in in the thread. As well as the target cell location and the output cell location.
Click to expand...
Thank you!!!
 
Upvote 0
My pleasure. Was it the CSE that made the first attempt wrong?
Best Wishes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
461
Peter_SSs
Peter_SSs
R
  • Question
Replace links after moving worksheets from one workbook to another.
Replies
2
Views
122
RockandGrohl
R
M
  • Solved
PDF creation of different sheet based on column value.
Replies
2
Views
268
mummbles74
M
T
  • Question
Compare spreadsheet/worksheet with changing rows
Replies
2
Views
94
BenW71
B
C
  • Question
Index/Match | Index/Small Formula
Replies
5
Views
348
Eric W
Eric W

Forum statistics

Threads
1,215,103
Messages
6,123,110
Members
449,096
Latest member
provoking

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