Replicate Excel index and match functionality

[Alex_Nesbitt]

Hello, I am looking for a formula that could help me achieve the same outcome that I could do in Excel using an index & match array formula.

I am trying to group classification data based on the hierarchy. You can see from my excel screenshot below, Column1 is what I am trying to achieve.

=INDEX([Code tite],MATCH([@[Grouping code]],[Code],0),1)

Here is a link to my airtable view: Airtable - Share
Here is a link to the excel: AirtableIndexMatchFormulaArray.xlsx

Thank you very much!!
Alex

[Bill_Felix]

Taking a brief look at your shared Table, maybe it is worth examining your first field…

If you can get away with having the ‘Code’ be your Primary Key/Field then you have some quick Airtable tricks available to you to simulate a Index/Match.

With ‘Code’ ( e.g. ‘Co_20_15_08’) as your first field, if you create a new Formula field:
LEFT(Code, 5)

This will give you the beginning 5 characters of ‘Code’ (e.g. Co_20’). Then, change the Field Type from Formula to Link to another record. Select your same Table. As soon as you do this, the field will be linked to the ‘grouping’ record in your question, from there you can ‘lookup’ things like ‘Code Title’, etc

So, was it what you asked for? :face_with_raised_eyebrow: Nah, but it might end up being more useful than your current setup.