Formula Moving Data To Second Page If Criteria in Range is Met

Question

I'm working on a Google Sheet Project that will move data from one page to another. I need the formula to search a range ( 'Booth Placement'!O2:O1000=133), if a cell is equal to the set value it will then write the data from the same row 'Booth Placement'!A2:A1000.

I know the IF can only work for one column and not a range spanning multiple columns. What should I switch the formula below?

=IF('Booth Placement'!O2:O1000=133,'Booth Placement'!A2:A1000,"")

I am trying to keep this formula as simple as possible since I will have to change the value it is searching for on each cell on the second page. I've googled this for two days and I'm pretty sure I'm just missing the obvious. Any/All Help is appreciated.

player0 · Accepted Answer · 2019-12-30 23:05:44Z

1

try:

=FILTER('Booth Placement'!A2:A1000; 'Booth Placement'!O2:O1000=133)

or:

=ARRAYFORMULA(IF('Booth Placement'!O2:O1000=133; 'Booth Placement'!A2:A1000; ))

answered Dec 30, 2019 at 23:05

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

George R Over a year ago

The first one works like a charm. The ARRAYFORMULA comes back as #REF when you add more than one to a page. I think its because I have multiples of the formula running inside the same coloum, just with different crtiera it is searching for.

user11982798 Over a year ago

but be carefull with filter, because some blank row or not match data will be ommited

user11982798 Over a year ago

And with filter, the result will be not in same row

user11982798 Over a year ago

in this matter I suggest you used the second one, except you don't need your result at destination page in same row.

George R Over a year ago

@player0 I started have some trouble in actual application. Both of these porovide errors when there would be more than one result in the same coloumn. I've attached a picture of the map it is moving info to based on booth number. imgur.com/a/9lcLbF2

Collectives™ on Stack Overflow

Formula Moving Data To Second Page If Criteria in Range is Met

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related