Hey everyone,
I’m trying to set up a system in Google Sheets where I can input either a code or a name, and the sheet will automatically fill in the other value. Here’s what I mean:
I have a list like this:
CODE | NAME
----------------
A101 | Red Apple
B202 | Green Pear
C303 | Blue Berry
When I’m entering data, I want to be able to type either the code or the name, and have the other column fill in automatically. Like this:
Input: Result:
CODE | NAME CODE | NAME
---------------- ----------------
A101 | A101 | Red Apple
| Blue Berry C303 | Blue Berry
I’m not great with formulas, but I thought maybe VLOOKUP could work? I’d need it to check both columns and fill in the blank one.
Any ideas on how to make this happen? Thanks a bunch for any help!
I’ve actually implemented something similar in my work spreadsheets, and it’s been a game-changer for data entry efficiency. While VLOOKUP is a good start, I found that combining it with INDEX and MATCH functions gives more flexibility. Here’s a formula that worked well for me:
=IF(A2<>“”, INDEX(Sheet1!$B$2:$B$100, MATCH(A2, Sheet1!$A$2:$A$100, 0)), INDEX(Sheet1!$A$2:$A$100, MATCH(B2, Sheet1!$B$2:$B$100, 0)))
This checks if there’s a code in column A, and if so, it finds the matching name. If not, it looks for the name in column B and returns the code. You’ll need to adjust the range (Sheet1!$A$2:$A$100) based on your data size.
One tip: maintain a separate ‘master’ sheet with your code-name pairs to keep your main sheet clean. It takes some setup, but it’s incredibly useful once it’s running.
hey, i’ve used somethin similar before. try this formula:
=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE),VLOOKUP(B2,Sheet1!$B$2:$A$100,2,FALSE))
it checks both columns and fills in the empty one. make sure to adjust the range (Sheet1!$A$2:$B$100) to fit ur data. hope this helps!
I’ve implemented a similar system using Google Apps Script, which offers more flexibility than formulas alone. Here’s a simplified version of the script I used:
function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
if (sheet.getName() != ‘YourSheetName’) return;
if (range.getColumn() == 1 || range.getColumn() == 2) {
var row = range.getRow();
var codeRange = sheet.getRange(row, 1);
var nameRange = sheet.getRange(row, 2);
var lookupSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘LookupSheet’);
var codes = lookupSheet.getRange(‘A:A’).getValues();
var names = lookupSheet.getRange(‘B:B’).getValues();
if (range.getColumn() == 1 && codeRange.getValue() != '') {
var index = codes.findIndex(r => r[0] == codeRange.getValue());
if (index != -1) nameRange.setValue(names[index][0]);
} else if (range.getColumn() == 2 && nameRange.getValue() != '') {
var index = names.findIndex(r => r[0] == nameRange.getValue());
if (index != -1) codeRange.setValue(codes[index][0]);
}
}
}
This script triggers on edit, checks the edited cell, and fills in the corresponding value from a lookup sheet. It’s more robust than formulas for large datasets and allows for easier maintenance of the code-name pairs.