# Google Sheets Array Formula Issue

Userlevel 1
• Beginner
• 15 replies

I have a formula which checks the numbers form Column C PRICE to see what range the number is in and puts the corresponding number Column M PRICE RANGE ID which works great as long as I want to copy to each row. I wanted to create and array so it automatically uses the formula when a row is added however my array never works correctly it only uses the number based on the first row Column C2. My array is in the Column Header.  I did try populating the row in my Zap with just the normal formula and it didnt work. Any help is appreciated.

=ARRAYFORMULA(IF(ROW(C1:C)=1,"Price Range ID", IF(ISBLANK(C1:C),"",IF(AND(C2>=1, C2<=150000),1,IF(AND(C2>=150001, C2<=250000), 2, IF(AND(C2>=250001, C2<=350000),3, IF(AND(C2>=350001, C2<=450000),4,IF(AND(C2>=450001, C2<=550000),5,IF(AND(C2>=550001, C2<=650000),5,IF(AND(C2>=6500001, 2<=750000),6,IF(AND(C2>=750001, C2<=900000),7,IF(AND(C2>=900001, C2<=1200000),8,0))))))))))))

RESULTS BELOW *** These should be different based on the corresponding C Column Price

 Price Range ID 5 5 5 5 5 5 5 5 5 5
icon

Best answer by Timtech 6 May 2021, 04:40

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

### 6 replies

Userlevel 7
+14

Hi @Timtech

Check the GSheets ARRAYFORMULA help article: https://support.google.com/docs/answer/3093275?hl=en

ARRAYFORMULAs need dynamic range references.

So change all instances of C2 to C2:C

Userlevel 1

Hi @Troy Tessalone TroyTessaloone

I actually had tried that already and just get all Zeros. Which to me means its not seeing the numbers in Column C in a range at all. However they are all formatted as numbers. Below is that formula and my Column C data as well.

=ARRAYFORMULA(IF(ROW(C1:C)=1,"Price Range ID", IF(ISBLANK(C1:C),"",IF(AND(C2:C>=1, C2:C<=150000), 1,IF(AND(C2:C>=150001, C2:C<=250000), 2, IF(AND(C2:C>=250001, C2:C<=350000),3, IF(AND(C2:C>=350001, C2:C<=450000),4, IF(AND(C2:C>=450001, C2:C<=550000),5, IF(AND(C2:C>=550001, C2:C<=650000),6, IF(AND(C2:C>=650001, C2:C<=750000),7, IF(AND(C2:C>=750001, C2:C<=900000),8, IF(AND(C2:C>=900001, C2:C<=1200000),9,0))))))))))))

 Price 500000 625000 155000 379000 325000 360000 210000 469000 350000 350000
Userlevel 7
+14

@Timtech

Try changing all of the C2:C to C1:C

Userlevel 1

@Troy Tessalone

No luck. I really thank you for you help. When I figure it out which I will i will post it up.

Userlevel 7
+12

Hi @Timtech

Would you mind providing some more context via screenshots of your zap and sheet?

Dynamic formulas can be inserted into Zapier via my method here:

Userlevel 1

Hi @GetUWired I really like what you have done there and might have a use for it as well. Below is how I actually got it to work yesterday. A very knowledgeable individual helped me out. Thank you as well @Troy Tessalone.

=arrayformula( ifs( row(C1:C) = 1, "Price Range ID", isblank(C1:C), iferror(1/0), C1:C < 1, 0, C1:C <= 150000, 1, C1:C <= 250000, 2, C1:C <= 350000, 3, C1:C <= 450000, 4, C1:C <= 550000, 5, C1:C <= 650000, 6, C1:C <= 750000, 7, C1:C <= 900000, 8, C1:C <= 1200000, 9, true, "out of range" ) )