-
Index/Match instead Vlookup
Hi good day. I just created a formula to grab dates from another source sheet. The situation here that i created using VLOOKUP and read that it is recommendable to use Index/Match for this kind of situations. Can you help me to build the formula and including if there is #NO MATCH show in blank? Thanks in advance. Below…
-
Can someone help me with this IF INDEX MATCH formula?
I want to make a formula that searches another sheet and if the 1st budget is "Philadelphia", then show 1st budget amount. Otherwise, show the second budget amount. Let me know if I am way off. Thanks! =IF(INDEX({1st_Budget}, MATCH([Row ID]@row, {Row_ID})) = “Philadelphia”, INDEX({1st Budget Amount Approved}, MATCH([Row…
-
Formula Help
I need a formula that says, if end date is one day away, and %complete is less than 50% done, then change the Health column cell to red. If %complete is 100%, then change Health column cell to blue, otherwise keep health column cell green. Can someone help?
-
Cross-reference formula comparing 2 cells to return a 3rd
I've been trying all the suggestions from the formula handbook and the community but I'm still doing something wrong. Basically, I have 2 sheets. The 1st sheet needs to have a column that returns a value from the 2nd sheet. But it should only return that value if the email address and item number match between the two…
-
Average collect w/ 3 criteria
I'm attempting to average the %age passed for a specific month and year for a specific team. I successfully used the below to average the total by month and year, but can't figure out how to add the team. =AVG(COLLECT([%Passed]:[%Passed], [Month 2]:[Month 2], =1, Year:Year, =2024) I tried the below, but am getting a…
-
Partial match with INDEX(DISTINCT(COLLECT(
Hello, I need to collect a list of values from my source sheet based on a search field (cell) in my target sheet. I have a very large subset of data that I'm pulling from, so I've divided it into 6 sheets to increase the speed of the lookup. Currently, I'm using the formula: =IFERROR(IF(OR(LEFT([Lookup Value]$2, 1) = "A",…
-
Can a COUNTIF formula "count" when there are multiple values in one cell?
I've created the following formulas to count how many times a person's name is appearing in a configurable (Contact List) column for Project Lead and then Assigned To on another sheet: =COUNTIF({Project Lead}, Primary@row) =COUNTIF({Assigned To}, Primary@row) The Primary@row is directing to the name of the person I have…
-
On Hover
Hello Community, I need your help on this. (Attached) On hover, I want to be able to see the name (list) of project each person is assigned to
-
Summarize the same column twice
Hi, I need to summarize a score column twice on a report. The first one should be the SUM of the Interview Score and the second one should be the AVERAGE of the same column (Interview Score). How can I do this?
-
Automatically check if
If any formula, after filling form check-in or out will be checked automatically if a person is checking in this day? I need to know if some ppl already check in or out from the another list (sheet).