Search code examples
if-statementgoogle-sheetslambdaimportgoogle-sheets-formula

How to execute the function only when there is a specific value in another column

Dear StackOverFlow users,

I would love to create a function that will do the calculation based on the specific value in another column.

enter image description here

SYMBOL NAME STOCK TYPE DIVIDEND
AAL American Airlines Group Inc Growth Do this "(SUBSTITUTE(IMPORTXML(CONCATENATE("https://finviz.com/quote.ashx?t=";r);'XML IMPORT'!C$3);".";",")/4)" only if C:C="Dividend" else "-"
T AT&T Inc. Dividend Do this "(SUBSTITUTE(IMPORTXML(CONCATENATE("https://finviz.com/quote.ashx?t=";r);'XML IMPORT'!C$3);".";",")/4)" only if C:C="Dividend" else "-"

Example xls is here:

https://docs.google.com/spreadsheets/d/1y6d0_0x_8aooy9iYfc9HtspwBpN6k5mJV5TKeOsnjcQ/edit?usp=sharing

Solution

  • use IF:

    =IF(C3="Dividend"; SUBSTITUTE(
 IMPORTXML("https://finviz.com/quote.ashx?t="&A3; 'XML IMPORT'!C$3); "."; ",")/4; "-")

    array would be:

    ={"DIVIDEND";INDEX(MAP(C2:C; A2:A; LAMBDA(c; a; IF(c="Dividend"; SUBSTITUTE(
 IMPORTXML("https://finviz.com/quote.ashx?t="&a; 'XML IMPORT'!C$3); "."; ",")/4; "-"))))}