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

#### halinc

##### New Member
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:

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!

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### jasonb75

##### Well-known Member
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.

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.

Last edited:

#### halinc

##### New Member
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

##### Well-known Member
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

##### New Member

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

#### jasonb75

##### Well-known Member
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

##### Well-known Member

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

##### New Member
This works great! Thank you very much for this!

#### jasonb75

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

Replies
8
Views
222
Replies
14
Views
315
Replies
10
Views
161
Replies
4
Views
362
Replies
1
Views
117

1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

### 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.

### Which adblocker are you using?

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

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