I have a column with ID numbers, and the first three characters indicate a label of interest I want to create in a separate column.
The first three characters of ID and labels are as follows:
182: turtles, 187: rabbits, 196: cats, and all the rest are "None". An example output which I did manually looks like this:
I’m a novice at excel, and attempted a formula which was something like this but couldn’t figure out how to finish it or make it work:
The actual data is too much to actually do manually. I’m open to whatever approach works. Thank you.
>Solution :
Try: Using XLOOKUP()
• Formula used in cell B2
=XLOOKUP(LEFT(A2,3)+0,{182,187,196},{"Turtles","Rabbits","Cats"},"None")
Or, You can #SPILL! as an array.
=XLOOKUP(LEFT(A2:A15,3)+0,{182,187,196},{"Turtles","Rabbits","Cats"},"None")
Few other alternatives:
• Formula used in cell C2
=IFERROR(IFS(LEFT(A2,3)+0=182,"Turtles",LEFT(A2,3)+0=187,"Rabbits",LEFT(A2,3)+0=196,"Cats"),"None")
• Formula used in cell D2
=SWITCH(LEFT(A2,3)+0,182,"Turtles",187,"Rabbits",196,"Cats","None")
• Formula used in cell E2
=IFERROR(CHOOSE(SUM(N(--LEFT(A2,3)={182,187,196})*{1,2,3}),"Turtles","Rabbits","Cats"),"None")