Arrayformula to check if column contains text and pull the number next to it. Google Sheets
In desperate need of some assistance with this! Wasn't sure how to title this question...
SAMPLE SHEET - CLICK ME! :)
In SupportingSheet!H1 I have the following formula:
=ArrayFormula(if(G1:G<>"", IF(DASHBOARD!N2<>"", G1:G/DASHBOARD!$P$2-filter(DASHBOARD!O1:O100,REGEXMATCH(DASHBOARD!N1:N100,E1:E100)),G1:G/(DASHBOARD!$M$3)),))
The part I struggle with is:
G1:G/DASHBOARD!$P$2-filter(DASHBOARD!O1:O100,REGEXMATCH(DASHBOARD!N1:N100,E1:E100))
It needs to divide two numbers and then subtract another number. I can't seem to get this formula to pull the correct number.
It needs to check if the text in E1:E100 exist in DASHBOARD!N1:N100, if yes, pull the number from DASHBOARD!O1:O100.
For example, text in SupportingSheet!E1 can be found in DASHBOARD!N2, hence it needs to pull the number from DASHBOARD!O2.
Column SupportingSheet!J has the actual end result that a formula needs to produce.
It doesn't look like Regexmatch works as an Arrayformula and I am not sure how to go about it.
Please note, that text in SupportingSheet!E1:E is not always identical. Often it will have a random number of "space" at the end (long story...). That is why Regexmatch was a perfect option until I realised it didn't work.
Please let me know if further clarification is needed.
Below is an image of the random spaces (non-printable characters) at the end.
use:
=ARRAYFORMULA(IF(G1:G="",,IF(DASHBOARD!N2<>"",
IFNA(G1:G/DASHBOARD!$P$2-VLOOKUP(E1:E1000, DASHBOARD!N1:O100, 2, 0),
G1:G/DASHBOARD!$M$3))))
- How to do if-else in Thymeleaf?
- awk: dealing with variable in if statement
- Using awk and if statement
- Check if a command has run successfully
- How to compare float numbers in an if conditional in BASH?
- How can I rewrite this block of if / Else if into a more structured, condensed and optimal way. (C#)
- Using arrayformula to add suffix, but would like to skip empty cells
- Go to next item in list based on if statement in foreach
- PBI - Year total value does not reflect the DAX I wrote
- Can an else if statement be used to create a step-wise reveal of elements with JavaScript?
- Logical Test in Image instance
- How can I make an "Unless condition" in an IF statement c++
- AND condition added to Array Formula doesn't work as expected
- Correct VBA Syntax for Nested If Then Else Statements? Compile error: Else without If
- switch case in XSL
- Get first non-empty cell in row
- An "and" operator for an "if" statement in Bash
- Checking for character in a string
- VB6 and VB.net has different outcomes with the same IF statements
- Returning Values to Razor Page - Confused Javascript or .cs?
- Excel VBA - how do I get the RowSource of a ComboBox to change depending on the value of another Combobox?
- comma operator in if condition
- How to assign values to a df$column from another df using conditions in R
- Else if statement to create a new column
- How can I run code If any one of if else statements is true?
- zsh condition on number results in bad pattern
- Cobol - checking for remainder in IF clause
- Can you store a variable inside a if-clause?
- How to use the @if angular with async pipe and alias for else block?
- Condition in python lists comprehensions