-
Help for INDEX/COLLECT with multiple criteria
Hi everyone! I usually use INDEX/MATCH, but I am trying to "match" multiple criteria and found that it is better to use INDEX/COLLECT when dealing with multiple criteria. I keep returning "#INVALID VALUE" and am confused what I am missing… I have an "Audience" sheet my team is using with employees' respective name, ID…
-
INDEX COLLECT MIN (again)
Had a follow up question to my previous question located here. — I was using the original sheets, which I fixed with the solution from that thread, as a template. Today, I copied them over to essentially create the same system for a different LOB. I checked the box that updated all formulas to the new references. Now, in…
-
Lookup Formula Not working
I am attempting to do a lookup of a a Date (on the Deliverable Tracker, shown in screenshot #1). The lookup will use the Effective Start and End dates to find the appropriate range, then the end result will be the text value column at the far right. My formula shows a blank result, here is the formula:…
-
INDEX / MATCH / CONTAINS Assistance
Looking for your help, the below formula works for the moment: =IFERROR(INDEX({SFDC Opps for Renewals Range 1}, MATCH(SFOP@row , {SFDC Opps for Renewals Range 2}, 0)), "No Match Found") However the column above actually contains multiple SFOPs and so I think I need to add a CONTAIN element to the formula so when SFOP…
-
Cross-reference sheets to find matches
Hi! I am trying to determine how many of our students are later hired by our institution. I have a Sheet with a Student Name column , and would like to add a "Hired" Column in this sheet that would cross reference to a second sheet (Master employee list) and flag if there is a match in the Employee Name column (so if a…
-
Troubleshoot Workflows/notification not going to specific people (index/match formula driven)
Hello! I have a universal intake sheet to manage a variety of request. Assigned approvers are populated based on index/match formulas using a contact column. The notifications are working for everyone except one person. Oddly enough, when I push the notification as a test (and notify me), I don't get the notification…
-
How to update INDEX formula criteria in a specific way?
My company has created a freelancer invoicing system using two sheets. It depends on an INDEX formula that we now need to improve, which I'm having trouble doing. Sheet #1: A database with information on all individual freelancer (aka subcontractors) contracts (aka SOWs) Sheet #2: A form-based tracker with notifications…
-
Index / Match
Looking for some guidance. Goal: If "KPI Availability %" @ row is equal to or greater than "Annual Score % Availability" row 10 then it will look at the "Previous Contract Year Event" @ row and match it with the column header 0 , 2,3,4,5,6,7,8 (individual columns). Then it will look down the matched column where it matched…
-
Index/Match formula using a range of values?
Hello, I'd like to create a formula or series of formulas to accomplish the following… There are two sheets: Source Sheet Return Sheet Source Sheet: Return Sheet: What I'd like to happen is… on the Return Sheet: When a value is entered in the Constituent Number column, the sheet returns a corresponding value in…
-
Listing cell contents from multiple cross reference fields in one cell (if statement, index/match?)
Hi, I am trying to do an if/index/match statement to pull the contents of a cross reference cell if it is not blank into a single cell. I need to be able to list several non-blank values with a paragraph break in between in a single cell and I am drawing a blank. Right now I have this: =IF({ParkingLotBlockedComm} <> "",…