Forum Discussion
How to look up specific text in a specific column, multiple columns involved
Hello,
I am trying to implement a search field for a vertically oriented list, lets name it #1 for reference, looking for the number of results of the desired text within a range in a specific column in separate ranged list, #2, which is horizontally oriented.
Neither list are Excel tables. Values in List 1 are automatically obtained and sorted from List 2 via dynamic functions which spill over underneath.
For better reference, I have implemented a simple sanitized example as an attachment, both in Workbook screenshot format.
Is it possible for this to be implemented, and how could I go about to do so?
Thanks in advance
I will assume you are using a simple =TRANSPOSE() on the rows in Table 2 to create the spill in the summary table (i.e. so we can avoid extra lookup/sorting). Then you just need a formula to do the counting in the lists. Here are 2 options:
Using LAMBDA helper BYCOL:
=LET(data,F4:.AAA100, TRANSPOSE(BYCOL(data,LAMBDA(c, SUM(--ISNUMBER(SEARCH($B$2,c)))))))Using MMULT to do a matrix sum:
=LET(data,F4:.AAA100, TRANSPOSE(MMULT(SEQUENCE(1,ROWS(data),1,0),--(ISNUMBER(SEARCH($B$2,data))))))since table 2 isn't a formal table I use the shortcut :. to do a TRIMRANGE and assumed an arbitrary max column of AAA and max row of 100. If the data exceeds that you will need to adjust and if there is other data on the sheet in that region you will need to adjust (one of the advantages of Defining as a Table instead of just using a range)
I will attached the updated sheet with both examples
2 Replies
Yes, you are correct about me transposing values in table 2 to get table 1.
I ended up solving the issue by using a combination of CELL, INDEX+MATCH, INDIRECT, ADDRESS, COLUMN and COUNTIF.
I will have a look at your formulas as well, though.
Thanks for taking your time to help.
I will assume you are using a simple =TRANSPOSE() on the rows in Table 2 to create the spill in the summary table (i.e. so we can avoid extra lookup/sorting). Then you just need a formula to do the counting in the lists. Here are 2 options:
Using LAMBDA helper BYCOL:
=LET(data,F4:.AAA100, TRANSPOSE(BYCOL(data,LAMBDA(c, SUM(--ISNUMBER(SEARCH($B$2,c)))))))Using MMULT to do a matrix sum:
=LET(data,F4:.AAA100, TRANSPOSE(MMULT(SEQUENCE(1,ROWS(data),1,0),--(ISNUMBER(SEARCH($B$2,data))))))since table 2 isn't a formal table I use the shortcut :. to do a TRIMRANGE and assumed an arbitrary max column of AAA and max row of 100. If the data exceeds that you will need to adjust and if there is other data on the sheet in that region you will need to adjust (one of the advantages of Defining as a Table instead of just using a range)
I will attached the updated sheet with both examples
