# Tag Info

Accepted

### Arrayformula when using index match

Here is the VLOOKUP formula adapted for the new sheet and column, with your concern about a hardcoded column index resolved. Formula =INDEX(IFNA( VLOOKUP(A2:A, 'Staff Effort'!A8:AW, COLUMN('Staff ...
• 6,551
Accepted

### Can you do an arrayformula when using index match?

Formula in 'Project Tracking' Clear Column B then put the formula in B1 ={"Project Name/Project Folder Link"; INDEX(IFNA( VLOOKUP(A2:A, 'Project List'!A2:B, COLUMN('...
• 6,551
Accepted

### Help converting counta to an array formula

To return a COUNTA for C:AU in rows 8 to the end of the sheet: =BYROW(C8:AU, LAMBDA(row, COUNTA(row))) To skip empty rows: =BYROW(C8:AU, LAMBDA(row, IFERROR(1/(1/COUNTA(row))))) BYROW  COUNTA  ...
• 6,551
1 vote

### My unique ID is not copying down when using arrayformula

Use open-ended references, like this: =arrayformula( if(len(B2:B), B2:B & substitute(left(C2:C, 3), "***", "AGN") & text(countifs(B2:B, B2:B, row(B2:B), "&...
• 15.1k
Accepted

### Google Sheets Conditional Formatting w/ Several Criteria

=REGEXMATCH(\$H2, "(?i)shop|warehouse")* NOT(REGEXMATCH(\$I2, "(?i)done"))* LET(arr,INDEX(\$A\$2:\$A& \$B\$2:\$B& \$C\$2:\$C& \$H\$2:\$H), cnt,INDEX(arr=INDEX(arr, ROW()-...
• 6,551

As of July 23, 2024 there is a feature called "Plot null values" which seems to fill in the gaps
• 101

### Google Sheets Conditional Formatting w/ Multiple Criteria

Conditional Format Custom Formula =NOT(REGEXMATCH(\$I2, "(?i)done")) * REGEXMATCH(""& \$H2, "(?i)shop|warehouse") * COUNTIFS(\$A\$2:\$A,\$A2, \$B\$2:\$B, \$B2, \$C\$2:\$C,\$C2,\$...
• 6,551
1 vote
Accepted

### How to pull all the data on the "right" of a column with certain value in the header

You can try: ={A:A,filter(A:H,sequence(1,columns(A:H))>=xlookup("*Qtr*",A1:H1,column(A1:H1),,2))} The xlookup returns the number of the column containing 'Qtr' and by evaluating using &...
• 1,230