Let’s say I always know my row will be 14.

I only know the column by another value above it.

For example, $Q$7 = A

I want to be able to take that value A and add it so that my formula is now $A$14.

I need a formula that finds the value of $Q$7 and places it as $A$14.

Then if I applied that to let’s say $D$5 where $D$5 = R the same formula would now equal $R$14.

Does that make sense?

I know to use = $A$14 in the cell but I want it to depend on the column.

Thanks for any help.enter image description hereenter image description here

  • Firstly, welcome to Ask Different! :) I hope you come to find this site has a lot to offer! In case you haven't already, it's worth taking the time to read the tour. Now to your question, to be honest it's not really clear what you're asking. Perhaps you could include a screenshot to clarify?
    – Monomeeth
    Commented Jul 19, 2019 at 1:30
  • Thanks for replying! I hope my screenshot helps clarify what I am trying to accomplish.
    – Steven
    Commented Jul 19, 2019 at 1:56
  • I have gotten as far as =T(E4)&””&14 but that only returns the value ‘A14’ and not the value ‘U’ from Cell A14.
    – Steven
    Commented Jul 19, 2019 at 1:58
  • Have you looked at indirect() ?
    – Solar Mike
    Commented Jul 19, 2019 at 6:38
  • Thanks for the screenshot - it's still not clear to me, but at least now I can ask some informed questions! :) Here goes: (1) In your scenario, is the value U a numerical value or...? (2) When you say you want to combine the value of E4 with the row number 14, what do you mean by 'combine'? (3) The last sentence in your screenshot says, So if I needed to find the value of F5, I would combine F4 and 14 to return the X in $A$5. Is that sentence correct? I ask only because I thought I understood what you wanted, but then that sentence throws me off.
    – Monomeeth
    Commented Jul 19, 2019 at 7:30

1 Answer 1


I would approach this a little differently to (hopefully) simplify things a little!

Let's assume you will use Rows 4, 7, 10, etc to enter the text of your words/sentences (which is the approach in your screenshot).

Let's also assume that you're only going to use Columns C to W to enter your text into (although there's no reason why it can't be more columns, but this is just what I gather from your screenshot).

For this answer, let's assume your Alphabet is in Row 50 and your Substituted letters are in Row 51.

In summary, this means:

  • the formula you need will have to be entered in every cell from C5 to W5, C8 to W8, C11 to W11, and so on
  • your alphabet will be in Row 50
  • your substituted letters will be in Row 51.

So, let's build this using the following steps:

  1. In Row 50 add the letters of the alphabet from A50 to Z50
  2. In Row 51 add your substituted letters
  3. Enter the following formula in cell of C5:

=IFERROR(LOOKUP(C4, $A$50:$Z$50,$A$51:$Z$51),"")

(The reason we're using IFERROR in the formula is so Numbers will ignore any spaces in your sentences, rather than displaying an ugly error icon.)

  1. Copy and paste the formula from cell C5 to your other cells (i.e. cells D5:W5, C8:W8, C11:W11, and so on)
  2. Now start entering your text to be ciphered into cells C4:W4, C7:W7, C10:W10 and so on
  3. As you enter the text, the ciphered result should appear in the cell below. If it's a space, the cell below will remain blank


  • Be careful to only include the letter and nothing else in each cell of your Alphabet row. Having a space or anything else will cause problems. For example, if you entered your E with a space after it, then Numbers will never find the E because it's looking for an 'E' and not a 'E ', if you get what I mean.
  • If you have the need to have no substitute value for some letters of the alphabet, then the result will be a 0 (i.e. a zero is displayed) instead of a blank cell. If this applies to you, you could create a conditional highlight to hide the result.
  • Personally, I would actually put your alphabet and substituted values in another worksheet, but this may not be necessary for your purposes.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .