could you please help me add several variables for each group in Excel?

We can see that there are three groups A, B, and C. There are 10 same variables for each group. Right now, I would like to add several variables for each group. Could you please help me with an efficient way? This is just a simple example. I need to add 5 more variables for each group in the real data. The thing is, I have already have the original data. "variable 1" is the simple version of variable name. Right now I have "variable 1" - "variable 10", I'm trying to add 5 more variables named "abc", "def", "ghi", "jkl", "mnq" for each group. Thanks very much!

CodePudding user response：

Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor. I Show In Bellow Mention Image
Formulas =VLOOKUP(B1,F:G,2,0) Sys

CodePudding user response：

Put the following formulas in the given cells and fill down as far as you need.

A2 = ="Variable " & IF(MOD(ROW() - 1,10) = 0, 10, MOD(ROW() - 1,10))

B2 = =IF(RIGHT(A2,1) = "1", SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN() COUNTIF($A$1:A2,"Variable 10") - 1), "1", ""), "$", ""), B1)

There could be a better way to do it but that works for me.