How to Insert Data By Category

Question

Hello, sir. So I want to fill data automatically to the 'Faculty' column like this

Study	Faculty
English Literature
Physics
German Literature
Mathematics
Chinese Literature
Literature Education
Alchemy
Computer Science

Using category of this:

Faculty of Literature	Faculty of Science & Technology
English Literature	Mathematics
German Literature	Physics
Chinese Literature	Alchemy
Literature Education	Computer Science

So it should look like this:

Study	Faculty
English Literature	Faculty of Literature
Physics	Faculty of Science & Technology
German Literature	Faculty of Literature
Mathematics	Faculty of Science & Technology
Chinese Literature	Faculty of Literature
Literature Education	Faculty of Literature
Alchemy	Faculty of Science & Technology
Computer Science	Faculty of Science & Technology

But its just an example. Actually, I have a bunch of data and category, and to write them one by one is taking so much time. If its there any way to input data automatically from the category I make (I didnt even know how to make 'category' on this app lmao) to the 'faculty' column. Thanks for your help and attention 🙏

Answer 1

Hi,

In cell B2, enter this formula

=FILTER($E$1:$F$1,BYCOL($E$2:$F$5=A2,LAMBDA(a,OR(a))))

Hope this helps.

Answer 2

With the set-up you could try a solution like in the picture below. I've used structured tables and the one with the faculty information is called 'LookupTable'.

Formula in B2 (fill automatically in the 'Faculty' column.

=INDEX(LookupTable[#Headers],,SUM(IFERROR(FIND([@Study],LookupTable),0)*{1,2}))