I have 2 sheets in a spreadsheet, 1 which is the list of all the words / definitions, like this:

enter image description here

And another that is a bunch of derived words, like this:

enter image description here

In the derived word sheet, I would like for it to place in the C column the "custom" word, plus the word "trut". Like {custom} trut. It pulls custom by using column B in the derivative sheet as a key to lookup the row in the main sheet. Then from the main sheet, it pulls the corresponding custom column value.

How can this be done? I know how to implement basic formulas, but not sure how to accomplish the templating, and how to use the input as a key.

1 Answer 1


Enter this formula in Cell C2 of the "derived words" sheet. It will automatically populate any matched words, and will display "not found" (you can change this) if a match can't be found.

=iferror(arrayformula(vlookup(B2:B8,{definitions!D2:D,definitions!C2:C},2,false)&" trut"),"not found")

  • The "range" for VLOOKUP is expressed as an array. This enables the formula to return a value to the left of the search column).

    • Reference: Using arrays in Google Sheets.
    • Normally the "range" in VLOOKUP is one or more adjacent/contiguous columns; the left-most column contains the search key and the "index" references the column number containing the value to return - this is a column to the right of the lookup column. BUT, in this question, we need to lookup a value in Column D and then return the value in Column C (to the left).
    • To do this, we create our own array by using brackets { }. The array consists of two columns: C & D but we reverse the order =>: `{definitions!D2:D,definitions!C2:C}.
    • VLOOKUP searches the first column (Column D) for the "search key" and returns the value from Column C.
  • &" trut" is the method to add "trut" to the value returned by VLOOKUP.

  • arrayformula - used to apply the formula to all the derived values in column C.

  • IFERROR - a troubleshooting method to avoid an error.

  • Works like a charm, can you explain more what is happening with the vlookup method? Also, how do I show it blank (or not found) without "trut" if col D is present but col C is not?
    – Lance
    Sep 29, 2022 at 19:42
  • Try this: =iferror(arrayformula(vlookup(B2:B8,{definitions!D2:D,definitions!C2:C},2,false)),"not found"). On a tangent, the derived words can be easily compiled from the definitions using a formula. For example, a QUERY.
    – Tedinoz
    Sep 30, 2022 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.