IFNA, Index & Match Help

PlusBob

New Member
Joined
Feb 16, 2016
Messages
15
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
So here's a mock short version of what I have.

Sheet 1 has a lot of personal and work related information. On other sheets I have products that are saved as PDFs for the masses. This example on Sheet 2 is a recall roster, in flow chart form (imagine lines and arrows showing hierarchy, etc.)

I want to be able to not really touch (make it automated). I want it to match the teams, then list the employees, and then the formula below to match the cell# to the employees.*
Example would be (in Sheet 2 Columns E to G), a formula is inserted in F10-F12 that matches F9 "A" (Team), and list all of the employees**.​
*I'm restricted in not using Macros and VBA due IT security policies (joy), so need a formula workaround.​
**Each team has a set number of positions (billets). So for example A Team has up to 3 peeps in their team. So I would copy & paste the formula into F10-F12.​

In cells like G10:G12, I use the following formula, which works great to match the employees and then their cell#.
=IFNA(INDEX(Employees!$A$5:$A$22,MATCH(F10,Employees!$D$5:$D$22,0)),"")

Thank you for taking the time to invest in my inquiry. I've been working on so many different options, I'm stumped.


ABCDEFGHIJ
1
2On Sheet 1Employees ListOn Sheet 2Recall
3
4NameTeamsWork #Cell #
Boss​
5CassidyC
(303) 555-8002​
(720) 777-3023​
6Dial-ToneC
(303) 555-8987​
(720) 777-6400​
Asst Boss​
7DukeB
(303) 555-4200​
(720) 777-4808​
8FaceA
(303) 555-7739​
(720) 777-7967​
TeamsTeams
Teams​
9FlaggB
(303) 555-6809​
(720) 777-1216​
A​
C​
E​
10FlintD
(303) 555-1887​
(720) 777-2410​
Face
(720) 777-7967​
11FrankelE
(303) 555-2525​
(720) 777-0545​
Hannibal
(720) 777-9836​
12GreerE
(303) 555-9358​
(720) 777-3421​
Snake-Eyes
(720) 777-2739​
13Gung-HoF
(303) 555-2178​
(720) 777-9765​
14HannibalA
(303) 555-4427​
(720) 777-9836​
BE
F​
15HawkB
(303) 555-4067​
(720) 777-4430​
16Lady JayeD
(303) 555-5217​
(720) 777-3268​
17LifelineE
(303) 555-4792​
(720) 777-5181​
18MainframeC
(303) 555-3695​
(720) 777-5408​
19RoadblockF
(303) 555-5789​
(720) 777-4894​
20ScarlettD
(303) 555-2866​
(720) 777-2861​
21ShipwreckF
(303) 555-8798​
(720) 777-6285​
22Snake-EyesA
(303) 555-6584​
(720) 777-2739​
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi Plusbob,

As you've 365 you could use FILTER but as I've only 2016 I will offer AGGREGATE.

Plusbob.xlsx
ABCD
1
2On Sheet 1Employees List
3
4NameTeamsWork #Cell #
5CassidyC(303) 555-8002(720) 777-3023
6Dial-ToneC(303) 555-8987(720) 777-6400
7DukeB(303) 555-4200(720) 777-4808
8FaceA(303) 555-7739(720) 777-7967
9FlaggB(303) 555-6809(720) 777-1216
10FlintD(303) 555-1887(720) 777-2410
11FrankelE(303) 555-2525(720) 777-0545
12GreerE(303) 555-9358(720) 777-3421
13Gung-HoF(303) 555-2178(720) 777-9765
14HannibalA(303) 555-4427(720) 777-9836
15HawkB(303) 555-4067(720) 777-4430
16Lady JayeD(303) 555-5217(720) 777-3268
17LifelineE(303) 555-4792(720) 777-5181
18MainframeC(303) 555-3695(720) 777-5408
19RoadblockF(303) 555-5789(720) 777-4894
20ScarlettD(303) 555-2866(720) 777-2861
21ShipwreckF(303) 555-8798(720) 777-6285
22Snake-EyesA(303) 555-6584(720) 777-2739
23
Employees


Cell Formulas
RangeFormula
B10:B12,F10:F12,D10:D12B10=IFERROR(INDEX(Employees!$A$5:$A$9999,AGGREGATE(15,6,ROW(Employees!$A$5:$A$9999)-ROW(Employees!$A$4)/(Employees!$B$5:$B$9999=B$9),ROW()-ROW(B$9))),"")
C10:C12,G10:G12,E10:E12C10=IFERROR(INDEX(Employees!$D$5:$D$9999,AGGREGATE(15,6,ROW(Employees!$A$5:$A$9999)-ROW(Employees!$A$4)/(Employees!$B$5:$B$9999=B$9),ROW()-ROW(B$9))),"")
B15:B17,F15:F17,D15:D17B15=IFERROR(INDEX(Employees!$A$5:$A$9999,AGGREGATE(15,6,ROW(Employees!$A$5:$A$9999)-ROW(Employees!$A$4)/(Employees!$B$5:$B$9999=B$14),ROW()-ROW($B$14))),"")
C15:C17,G15:G17,E15:E17C15=IFERROR(INDEX(Employees!$D$5:$D$9999,AGGREGATE(15,6,ROW(Employees!$A$5:$A$9999)-ROW(Employees!$A$4)/(Employees!$B$5:$B$9999=B$14),ROW()-ROW($B$14))),"")
 
Upvote 0
Solution
Or using 365
+Fluff 1.xlsm
ABCD
1NameTeamsWork #Cell #
2CassidyC(303) 555-8002(720) 777-3023
3Dial-ToneC(303) 555-8987(720) 777-6400
4DukeB(303) 555-4200(720) 777-4808
5FaceA(303) 555-7739(720) 777-7967
6FlaggB(303) 555-6809(720) 777-1216
7FlintD(303) 555-1887(720) 777-2410
8FrankelE(303) 555-2525(720) 777-0545
9GreerE(303) 555-9358(720) 777-3421
10Gung-HoF(303) 555-2178(720) 777-9765
11HannibalA(303) 555-4427(720) 777-9836
12HawkB(303) 555-4067(720) 777-4430
13Lady JayeD(303) 555-5217(720) 777-3268
14LifelineE(303) 555-4792(720) 777-5181
15MainframeC(303) 555-3695(720) 777-5408
16RoadblockF(303) 555-5789(720) 777-4894
17ScarlettD(303) 555-2866(720) 777-2861
18ShipwreckF(303) 555-8798(720) 777-6285
19Snake-EyesA(303) 555-6584(720) 777-2739
Sheet1


+Fluff 1.xlsm
ABCD
1NameTeamsWork #Cell #
2CassidyC(303) 555-8002(720) 777-3023
3Dial-ToneC(303) 555-8987(720) 777-6400
4DukeB(303) 555-4200(720) 777-4808
5FaceA(303) 555-7739(720) 777-7967
6FlaggB(303) 555-6809(720) 777-1216
7FlintD(303) 555-1887(720) 777-2410
8FrankelE(303) 555-2525(720) 777-0545
9GreerE(303) 555-9358(720) 777-3421
10Gung-HoF(303) 555-2178(720) 777-9765
11HannibalA(303) 555-4427(720) 777-9836
12HawkB(303) 555-4067(720) 777-4430
13Lady JayeD(303) 555-5217(720) 777-3268
14LifelineE(303) 555-4792(720) 777-5181
15MainframeC(303) 555-3695(720) 777-5408
16RoadblockF(303) 555-5789(720) 777-4894
17ScarlettD(303) 555-2866(720) 777-2861
18ShipwreckF(303) 555-8798(720) 777-6285
19Snake-EyesA(303) 555-6584(720) 777-2739
Sheet1
 
Upvote 0
Sorry, I was out for awhile and it wasn't until I had returned that I'd finally got a chance to test this out. This works fantastically! Now, I'm backwards engineering so that I can better understand it (IFERROR), and apply it to similar worksheets. Thank you very much!
 
Upvote 0
Sorry, I was out for awhile and it wasn't until I had returned that I'd finally got a chance to test this out. This works fantastically! Now, I'm backwards engineering so that I can better understand it (IFERROR), and apply it to similar worksheets. Thank you very much!
You're welcome! Thanks for getting back to us.

The AGGREGATE does the heavy lifting and the IFERROR is just a way to trap the #NUM! error when AGGREGATE runs out of matches.

As you've 365 then it's a pity @Fluff posted Sheet1 twice and so you missed his 365 solution. If you see him posting again in this thread I recommend you take a look as it will be more elegant than my offering.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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