Find row in Cloud Excel table matching text

Occasional Contributor

I have 2 identifiers in rows in Excel and a block of text.  I want get the row of data in Excel that has the identifiers in the text. For example, if I have text that says, "I love peach cobbler and pork dumplings," I would return row 3 from the table below.  Currently, I'm running an Office Script from Power Automate to iterate through the rows looking for the first criteria.  If that's found, it checks for the second criteria.  If it finds both, break the loop. Is there a way to use table autofilters or something else to do this more efficiently?

 

Row #Favorite FruitFavorite MeatFavorite Sport
1AppleSteakSoccer
2CherryChickenSwimming
3PeachPorkSkiing
4 Replies

@jiwhiteff 

That could be like

image.png

Screenshot is for Excel for Web.

Is there a way to do that in Typescript, not Excel formulas? I have multiple calls running at the same time. I don't want to populate temporary cells or tables because that will slow things down considerably.

@jiwhiteff 

Sorry, I'm not very familiar with the ExcelScript. As a minimum you may

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	let formula = "=REDUCE($C$1, SEQUENCE(ROWS(data)), LAMBDA(a,v, IF(SUM(--ISNUMBER(SEARCH( INDEX( data, v, {1,2} ), $A1))) = 2, a & INDEX(data, v, 3),a)))";
	selectedSheet.getRange("D1").setFormulaLocal(formula);
}

I took C1 as blank cell, don't know how to add empty string within text.

Not sure which temporary cells do you mean, if where the text is it could be wrapped by another lambda with text as parameter. 

 

To minimize reads and writes, which slow down the script the most, I read the table and traverse it in Typescript as a 2D array. I can read each row and calculate based on that. I traverse the rows until I find a match on the first criteria, then I filter on that result and search for the second criteria. If there was some way that I could match both criteria at the same time, that would be better.