How to reference a formula as text in one cell in a different formula

[halinc]

Hi,
I have a very long formula using nested IF formulas. It is basically checking different columns showing management chain levels and returning a value if a certain manager is in it, and if not, continuing down the IF chain. There are a few pieces that are repeated many times that I would like to just put in one cell, then reference that cell, to minimize typing (and I have to use this in some other contexts).

My formula is something like this:
=IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 04",$1:$1,0),4,1))="joe smith","Org 1",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 05",$1:$1,0),4,1))="Lisa Smith","Org 2",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))="Tim Jones","Org 3",etc.

These pieces are repeated many times:

INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 04",$1:$1,0),4,1))

INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 05",$1:$1,0),4,1))

INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))

I tried putting those text pieces in different cells and just referencing the cells, so it would look something like this:

=IF(A1="joe smith","Org 1",IF(A2="Lisa Smith","Org 2",IF(A3="Tim Jones","Org 3",etc.

But it does not seem to work.

Any suggestions would be much appreciated!

 

[jasonb75]

If each piece results in an individual value then you can put an = sign in front of it and refer to the result in your formula.
If a piece results an a multi cell range / array then you can create a named range and enter the piece as a formula (preceded with an = sign) into the named range'Refers to' box.
You can not, however, enter a piece of a formula into a cell as a text string and refer to it in the same way that you refer to a text range with indirect.

I'm sure that if you posted a visual representation of your data source and expected results (using XL2BB please, not screen captures) that somebody could come up with a very short alternative for what you're trying to do.

From the example you have given, the use of INDIRECT appears to be unnecessary, a lookup table would probably be a much more efficient solution.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Please also update your profile to show which version of excel that you use by clicking your username in the top right corner, then scrolling down, checking the correct box, scrolling to the bottom and saving changes. Please don't check all of the boxes, just the version that you are most likely to need suggestions to work with.

 

[halinc]

I basically have to apply this across many different sheets, and the column of the Management Chain fields changes in each of them. That is why I have those formula pieces: to find which column they are in automatically, so I can paste from one sheet to another without having to change hardcoded column references.

I see what you mean about INDIRECT, I could skip the whole indirect="text" and just vlookup for the text.

 

[jasonb75]

As I said above, a visual example would help us to identify ways that will work, I imagine that you're overthinking the task and that a very short generic formula could be used in combination with a table that contains a list of Management Chain Level #, Name and Org #

 

[halinc]

I've posted a small sample below.
To clarify, the reason I have the formulas to look up which columns the different mgmt chain fields are in is because I need to use them in several other sheets where each sheet has the chain values in different columns, and this way I can copy/paste without changing references. And this does work as it is currently. I just often am asked to make changes/additions, so thought it would make things easier and clearer if I could just reference another cell instead of pasting in a big "INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 04",$1:$1,0),4,1))" chunk every time. Though obviously I would be open to a different solution if there was something better than all these nested IFs!

Demo.xlsx
	A	B	C	D
1	Org	Management Chain - Level 04	Management Chain - Level 05	Management Chain - Level 06
2	Org D	James Platt (0597092)	Joe Propati (0077631)	David Mahler (0771628)
3	Org D	James Platt (0597092)	Leonora Siccardi (0522115)	Ann Field (0794197)
4	Org E	Rajeev Khanna (0685239)	Sanjay Yadav (67689)	Michael Blumenthal (0782116)
5	Org D	James Platt (0597092)	Joe Propati (0077631)	Kevin Johnson (0560410)
6	Org D	James Platt (0597092)	Leonora Siccardi (0522115)	Ann Field (0794197)
7	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
8	Org D	James Platt (0597092)	Joe Propati (0077631)	Peter Robertson (0777534)
9	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
10	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
11	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
12	Org B	Anthony Belfiore (0680015)	Jim Figliuolo (0710302)	Theresa Enright (0529167)
13	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)
14	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
15	Org E	Rajeev Khanna (0685239)	Raj Pillai (0697724)
16	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)
17	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
18	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
19	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
20	Org E	Rajeev Khanna (0685239)	Steven Dsouza (0780806)	Simon Sankey (27168)
21	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)
22	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Robert Olson (0534698)
23	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Brotzel (0592378)
24	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Brotzel (0592378)
25	Org E	Rajeev Khanna (0685239)	Mohan Putcha (0715774)	Dennis Wang (0681029)
26	Org E	Rajeev Khanna (0685239)	Mohan Putcha (0715774)
27	Org B	Anthony Belfiore (0680015)	Jim Figliuolo (0710302)	Kevin Allchorne (0556450)
28	Org A	Michael Benvenuto (0532569)	Jason Cesta (0513227)	Hillary Dallas (0757272)
29	Org A	Michael Benvenuto (0532569)	Melissa Warneke (0557601)
30	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Anthony Spinella (0770352)
31	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
32	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
33	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
34	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Anthony Spinella (0770352)
35	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
36	Org B	Anthony Belfiore (0680015)	Richard Astill (0512455)
37	Org A	Michael Benvenuto (0532569)	Andy Seyller (0587739)	Mohammed Jaffer (0547210)
38	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
39	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
40	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
41	Org A	Michael Benvenuto (0532569)	Melissa Warneke (0557601)	Diane Lowe (0681002)
42	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
43	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
44	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Rakesh Kumar (0757502)
45	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Rakesh Kumar (0757502)
46	Org E	Rajeev Khanna (0685239)	Raj Pillai (0697724)	Missy Katz (0152213)
47	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Patty Crawford (0122961)
48	Org D	Maggie Westdale (0537013)	Bill Hooper (0036771)	Andrew Howlett (0536208)
Sheet1

Cell Formulas
Range	Formula
A2:A48	A2	=IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 04",$1:$1,0),4,1))="Michael Benvenuto (0532569)","Org A",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 04",$1:$1,0),4,1))="Anthony Belfiore (0680015)","Org B",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 04",$1:$1,0),4,1))="Rajeev Khanna (0685239)","Org E",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 05",$1:$1,0),4,1))="Kirk Behrens (0494138)","Org C",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 05",$1:$1,0),4,1))="Selva Vaidiyanathan (0748939)","Org F",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 04",$1:$1,0),4,1))="James Platt (0597092)","Org D",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))="Nathan Shanaghy (0549590)","Org D",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))="Andrew Howlett (0536208)","Org D",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))="Andrew Taylor (0585755)","Org D",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))="Nick Quinn (0532315)","Org D",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))="Paul Norris (0560044)","Org D",IF(INDIRECT(ADDRESS(ROW(),MATCH("Management Chain - Level 06",$1:$1,0),4,1))="Paul D'Arcy (0536129)","Org D","_n/a"))))))))))))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$D$48	A2:A48

 

[jasonb75]

Is there any logic to the order of the names in the formula? I can make it a lot shorter with some consistency, but I need to be able to understand the rules behind which chain comes out on top when there are multiple matches.

As an example, on rows 7, 9, 10 and 11, Org D and Org F would both be valid results if each chain is checked individually. Why in these cases does Level 5 take priority over Level 4, but in rows 13, 14, 16, 17, 18, 19, 21 and 22 Level 4 takes priority over level 5?

 

[jasonb75]

Ignore the above, I had another look and realised that I was looking at the wrong column As far as I can see, the higher chain number takes priority, which is what I've based this on.

The table in columns F:H is a unique list of all names under the relevant Management Chain Level. I've located it in the same sheet for ease of testing but for proper use I would suggest having it in a separate sheet.

Also for testing, I've limited parts of the formula to B$1:D$1 and B2:D2, this was only done to prevent the formula from picking up the table in columns F:H, once that is moved these can be changed to $1:$1 and 2:2 respectively in order to match your original requirement.

25.08.20.xlsm
	A	B	C	D	E	F	G	H
1	Org	Management Chain - Level 04	Management Chain - Level 05	Management Chain - Level 06		Management Chain - Level 04	Management Chain - Level 05	Management Chain - Level 06
2	Org D	James Platt (0597092)	Joe Propati (0077631)	David Mahler (0771628)		Michael Benvenuto (0532569)	Kirk Behrens (0494138)	Nathan Shanaghy (0549590)
3	Org D	James Platt (0597092)	Leonora Siccardi (0522115)	Ann Field (0794197)		Org A	Org C	Org D
4	Org E	Rajeev Khanna (0685239)	Sanjay Yadav (67689)	Michael Blumenthal (0782116)		Anthony Belfiore (0680015)	Selva Vaidiyanathan (0748939)	Andrew Howlett (0536208)
5	Org D	James Platt (0597092)	Joe Propati (0077631)	Kevin Johnson (0560410)		Org B	Org F	Org D
6	Org D	James Platt (0597092)	Leonora Siccardi (0522115)	Ann Field (0794197)		Rajeev Khanna (0685239)		Andrew Taylor (0585755)
7	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)		Org E		Org D
8	Org D	James Platt (0597092)	Joe Propati (0077631)	Peter Robertson (0777534)		James Platt (0597092)		Nick Quinn (0532315)
9	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)		Org D		Org D
10	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)				Paul Norris (0560044)
11	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)				Org D
12	Org B	Anthony Belfiore (0680015)	Jim Figliuolo (0710302)	Theresa Enright (0529167)				Paul D'Arcy (0536129)
13	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)				Org D
14	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
15	Org E	Rajeev Khanna (0685239)	Raj Pillai (0697724)
16	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)
17	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
18	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
19	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
20	Org E	Rajeev Khanna (0685239)	Steven Dsouza (0780806)	Simon Sankey (27168)
21	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)
22	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Robert Olson (0534698)
23	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Brotzel (0592378)
24	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Brotzel (0592378)
25	Org E	Rajeev Khanna (0685239)	Mohan Putcha (0715774)	Dennis Wang (0681029)
26	Org E	Rajeev Khanna (0685239)	Mohan Putcha (0715774)
27	Org B	Anthony Belfiore (0680015)	Jim Figliuolo (0710302)	Kevin Allchorne (0556450)
28	Org A	Michael Benvenuto (0532569)	Jason Cesta (0513227)	Hillary Dallas (0757272)
29	Org A	Michael Benvenuto (0532569)	Melissa Warneke (0557601)
30	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Anthony Spinella (0770352)
31	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
32	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
33	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
34	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Anthony Spinella (0770352)
35	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
36	Org B	Anthony Belfiore (0680015)	Richard Astill (0512455)
37	Org A	Michael Benvenuto (0532569)	Andy Seyller (0587739)	Mohammed Jaffer (0547210)
38	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
39	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
40	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
41	Org A	Michael Benvenuto (0532569)	Melissa Warneke (0557601)	Diane Lowe (0681002)
42	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
43	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
44	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Rakesh Kumar (0757502)
45	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Rakesh Kumar (0757502)
46	Org E	Rajeev Khanna (0685239)	Raj Pillai (0697724)	Missy Katz (0152213)
47	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Patty Crawford (0122961)
48	Org D	Maggie Westdale (0537013)	Bill Hooper (0036771)	Andrew Howlett (0536208)
Sheet30

Cell Formulas
Range	Formula
A2:A48	A2	=IFERROR(INDEX($H:$H,MATCH(INDEX(B2:D2,MATCH("* Level 06",B$1:D$1,0)),$H:$H,0)+1), IFERROR(INDEX($G:$G,MATCH(INDEX(B2:D2,MATCH("* Level 05",B$1:D$1,0)),$G:$G,0)+1), IFERROR(INDEX($F:$F,MATCH(INDEX(B2:D2,MATCH("* Level 04",B$1:D$1,0)),$F:$F,0)+1),"N/A")))

 

[halinc]

This works great! Thank you very much for this!

 

[jasonb75]

You're welcome

It's not exactly a conventional way of setting up the table but for what you need, I think it is quite effective. I had another go and came up with the method below which uses a more conventional layout for the reference table and a shorter formula. It looks right, but I haven't checked the results in detail.

As with most things in excel, there are a few ways to achieve the desired result.

25.08.20.xlsm
	A	B	C	D	E	F	G
1	Org	Management Chain - Level 04	Management Chain - Level 05	Management Chain - Level 06		Name	Org
2	Org D	James Platt (0597092)	Joe Propati (0077631)	David Mahler (0771628)		Kirk Behrens (0494138)	Org C
3	Org D	James Platt (0597092)	Leonora Siccardi (0522115)	Ann Field (0794197)		Selva Vaidiyanathan (0748939)	Org F
4	Org E	Rajeev Khanna (0685239)	Sanjay Yadav (67689)	Michael Blumenthal (0782116)		Nathan Shanaghy (0549590)	Org D
5	Org D	James Platt (0597092)	Joe Propati (0077631)	Kevin Johnson (0560410)		Andrew Howlett (0536208)	Org D
6	Org D	James Platt (0597092)	Leonora Siccardi (0522115)	Ann Field (0794197)		Andrew Taylor (0585755)	Org D
7	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)		Nick Quinn (0532315)	Org D
8	Org D	James Platt (0597092)	Joe Propati (0077631)	Peter Robertson (0777534)		Paul Norris (0560044)	Org D
9	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)		Paul D'Arcy (0536129)	Org D
10	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)		Michael Benvenuto (0532569)	Org A
11	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)		Anthony Belfiore (0680015)	Org B
12	Org B	Anthony Belfiore (0680015)	Jim Figliuolo (0710302)	Theresa Enright (0529167)		Rajeev Khanna (0685239)	Org E
13	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)		James Platt (0597092)	Org D
14	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
15	Org E	Rajeev Khanna (0685239)	Raj Pillai (0697724)
16	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)
17	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
18	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
19	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Luse (94115)
20	Org E	Rajeev Khanna (0685239)	Steven Dsouza (0780806)	Simon Sankey (27168)
21	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Teffani Zadeh (0493853)
22	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Robert Olson (0534698)
23	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Brotzel (0592378)
24	Org C	James Platt (0597092)	Kirk Behrens (0494138)	Paul Brotzel (0592378)
25	Org E	Rajeev Khanna (0685239)	Mohan Putcha (0715774)	Dennis Wang (0681029)
26	Org E	Rajeev Khanna (0685239)	Mohan Putcha (0715774)
27	Org B	Anthony Belfiore (0680015)	Jim Figliuolo (0710302)	Kevin Allchorne (0556450)
28	Org A	Michael Benvenuto (0532569)	Jason Cesta (0513227)	Hillary Dallas (0757272)
29	Org A	Michael Benvenuto (0532569)	Melissa Warneke (0557601)
30	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Anthony Spinella (0770352)
31	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
32	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
33	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
34	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Anthony Spinella (0770352)
35	Org B	Anthony Belfiore (0680015)	Joe Martinez (0691106)	Melanie Budd (0529785)
36	Org B	Anthony Belfiore (0680015)	Richard Astill (0512455)
37	Org A	Michael Benvenuto (0532569)	Andy Seyller (0587739)	Mohammed Jaffer (0547210)
38	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
39	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
40	Org A	Michael Benvenuto (0532569)	Shardul Oza (0550006)	Ewelina Mazurkiewicz (88210)
41	Org A	Michael Benvenuto (0532569)	Melissa Warneke (0557601)	Diane Lowe (0681002)
42	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
43	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Mark Schmalzer (0028639)
44	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Rakesh Kumar (0757502)
45	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Rakesh Kumar (0757502)
46	Org E	Rajeev Khanna (0685239)	Raj Pillai (0697724)	Missy Katz (0152213)
47	Org F	James Platt (0597092)	Selva Vaidiyanathan (0748939)	Patty Crawford (0122961)
48	Org D	Maggie Westdale (0537013)	Bill Hooper (0036771)	Andrew Howlett (0536208)
Sheet30

Cell Formulas
Range	Formula
A2:A48	A2	=IFERROR(LOOKUP("zzz",VLOOKUP(T(IF({1},INDEX(B2:D2,MATCH(T(IF({1},"* Level 0"&{4,5,6})),$B$1:$D$1,0)))),$F:$G,2,0)),"N/A")