Input and Return

[LucasAssis]

Good morning,

I'm trying to create a database of exam questions and their parameters. All questions have skills and competencies, where, for example:

C = Competence; H = Ability

• Competence 1 = Abilities: 1,2,3
• Competence 2 = Abilities: 4,5,6
• Competence 3 = Abilities: 7,8,9

What I'm looking for is an automatic way to enter the ability, as I already have this data, and have Excel provide me with the competence related to that ability. And it should be scalable, since the expected data input for this spreadsheet is at least 5,000 items.

Please find an example below of what the spreadsheet would look like. I use both Microsoft Office Professional Plus 2016 Excel and Google Sheets, so I'm open to suggestions on whichever solution is more viable.

I would appreciate any assistance, and thank you for your time!

[#2 · Accepted solution]

@LucasAssis 

In F4:

=IF(G4="", "", QUOTIENT(G4-1, 3)+1)

Fill down as far as you want.