INDEX MATCH Arrays to create dynamic lists? Suggested Honeycode best practice?

Community Discussions
,
1

Hi, I have a lot of spreadsheets where I have large lists with multiple values assigned.

These lists update and may have new data added or removed.

I normally extract information of the master data source to create curated dynamically updated lists of smaller datasets using INDEX MATCH formulas entered using ctrl+shift+enter to enable an array.

In Honeycode to replicate the INDEX MATCH Array capabilities what do you recommend using?

Filter()
FilterDiff()
Or something else?
Or can we use arrays in Honeycode?

Thanks!

2

+1
I would be interested too in that answer :slight_smile:

3

actually it was not exactly the same problem for me. I solved mine.

7

Hi leon, thanks for the question and welcome to the community. I think there are two questions in here that I'll try to answer separately.

(1) Representing arrays in Honeycode
You describe a large list where each item has multiple items assigned to it. In Honeycode you'd model this with a joining table. Imagine you had a table of students and another table of classes. To represent students within each class you would create a new table with two columns, one with a rowlink to the particular class and the other column with rowlink to the particular students.

(2) Finding and extracting rows.
Instead of using INDEXMATCH or the similar VLOOKUP function, in Honeycode you use FINDROW. If you wanted to look up a particular class you could use FINDROW(class, "condition"). If you wanted to get all the students in that particular class you just found, you could nest that FINDROW function within a filter. For example, FILTER(student_class_join_table, "student_class_join_table[class] = FINDROW(class, ""condition"")"

1 Like