# Thread: Formula Question Thanks: 0 Likes: 0

1. ## Formula Question

Hi Guys,

Could someone explain how I would merge these two formulas below so that it looks in one range and returns ROUTE, then looks in another and returns COLLECTIONS, I'm slowly getting my head around formulas but bringing them together is what I struggle with.

=IF(\$B8="","",IFERROR(IF(MATCH(RIGHT(\$B8,3),Mon!\$E\$5:\$E\$100,0),"ROUTE",),"."))
=IF(\$B8="","",IFERROR(IF(MATCH(RIGHT(\$B8,3),Mon!\$E\$101:\$E\$150,0),"COLLECTIONS",),"."))

Thanks

2. ## Re: Formula Question

What do you want the cell to output exactly, "ROUTE COLLECTIONS"? in one cell?

3. ## Re: Formula Question

It's difficult to say exactly how to do this without seeing how your data is set up, and without knowing more about what you want to do exactly.

But perhaps something like this . . . .

=IF(\$B8="","",IFERROR(IF(MATCH(RIGHT(\$B8,3),Mon!\$E\$5:\$E\$100,0),"ROUTE",),".")&IFERROR(IF(MATCH(RIGHT(\$B8,3),Mon!\$E\$101:\$E\$150,0),"COLLECTIONS",),"."))

4. ## Re: Formula Question

Originally Posted by tyija1995
What do you want the cell to output exactly, "ROUTE COLLECTIONS"? in one cell?
Hi,

No, I have one sheet which staff input to, this has vehicle registrations against route numbers which are input daily. I have another sheet on the same workbook which is locked down and it contains every list of vehicle registrations that we have on site and this formula looks it up from the working page and returns "ROUTE" next to any reg which has been used that day, I also have a section where we have collection routes so when a registration is input to the collection routes, I want it to return "COLLECTIONS" instead of "ROUTE" and vice versa which is why I require it to look in two ranges....but dont know how

5. ## Re: Formula Question

Originally Posted by Gerald Higgins
It's difficult to say exactly how to do this without seeing how your data is set up, and without knowing more about what you want to do exactly.

But perhaps something like this . . . .

=IF(\$B8="","",IFERROR(IF(MATCH(RIGHT(\$B8,3),Mon!\$E\$5:\$E\$100,0),"ROUTE",),".")&IFERROR(IF(MATCH(RIGHT(\$B8,3),Mon!\$E\$101:\$E\$150,0),"COLLECTIONS",),"."))
Thanks for the reply Gerald, I've tested it but it returns ..

6. ## Re: Formula Question

Well I think that might depend on what your other inputs are.

I was able to get it to sometimes return "ROUTE.COLLECTION" or something similar.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•