How to get array value from array search key?
I want to get the total productivity of the employee according to the selected SPV in 1 cell, with multiple search key (array). I tried the "xlookup" formula, but it only shows the 1st array, whereas there are a lot productivity from the employee.
This is the formula, which only shows the 1st array.
ARRAYFORMULA(XLOOKUP(FILTER(A3:A9,B3:B9=E3),A13:A48,B13:B48))
Solution
You may try:
=sum(ifna(filter(B13:B,xmatch(A13:A,filter(A3:A9,B3:B9=E3)))))
OR
=sumif(index(xmatch(A13:A48,filter(A3:A9,B3:B9=E3))^0),1,B13:B)
- Sort Sheet Range in Natural Order
- how to display Over due or not over due automatically according to days
- Unable to send an email with a reference image with GAS
- google sheets array formula for boolean logic based on duplicate numbers per cell
- Sort alphanumeric data in natural order
- Counting a specific value from the past 7/30 days
- Exception: The parameters (String,String,null,(class)) don't match the method signature for CalendarApp.Calendar.createEvent
- How do I write a function to validate a UK Postcode in a Web App
- google sheets api key missing when using oAuth2
- Google sheets formula to find a cell in a 2d array, and get its index?
- How to create grouped serial numbers using Arrayformula?
- google sheets query left join one-to-many
- how to postpone certain holidays that fall on weekend
- Using ceiling function within Google Sheets QUERY
- Google Sheets Map LAMBDA function
- How to sort a 2d array in Apps script (google sheet) base on a 1d array
- INNER JOIN in Google Spreadsheets
- Control existing check boxes depending on values in another column
- How to run an onEdit function across multiple Google Sheets using a shared Apps Script library
- How to retrieve a Google Drive file link in Google Sheets based on a filename in a specific folder using Apps Script
- How to stop Google Drive from automatically saving csv's imported into a Google Sheets document
- Google sheets sumif using date
- Understanding apps script api calls?
- Counting cells that match another cell using address match to determine the range
- Processing multiple .csv files
- Google Sheets - Sorting separate date and time cells
- How to sync data between a Google Sheet and a Mysql DB?
- How to address the current cell in conditional format custom formula?
- Creating a dropdown using Google Apps Script. Is the method for changing the colours missing or not available yet?
- How do I locate a Google SpreadSheet ID?