Formula Question

[Kirstym1918]

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

 

[tyija1995]

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

 

[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",),"."))

 

[Kirstym1918]

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

 

[Kirstym1918]

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 ..

 

[Gerald Higgins]

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.