g suite – Displaying a “friendly name” for an ID in Google Sheets


I have a spreadsheet in Google Sheets which contains information split across several sheets. To keep things consistent between sheets, I use numbers as IDs, like (very simplified example):

  • Users: User ID, Name, Address,
  • Products: Product ID, Product Name
  • Orders: Order ID, User ID, Product ID

This works fine, but the “orders” sheet is hard to read at a glance and hard to add new items to because it’s just a random list of numbers. What I’d like is to have a more user-friendly interface on top of the data, so that the orders table shows something more like “Order ID, User Name, Product Name” so that it’s easy to read and I can add a row by just knowing the names, without having to look up the correct IDs.

What I want is for under the hood it to store the actual IDs so the table doesn’t break if we e.g. rename a product. Is there a good pattern for accomplishing this in Sheets?