Returning a cell with from a range a data

[Severno]

I will try and explain this as simple as I can. I have six stores in a row. Let's call them Boston, New York, Orlando, Houston, Dallas and LA.

LA has a delivery window of 10 to 11

I want to return the delivery window in a cell while the formula is reading the row. LA could be the first stop or last stop. It could be anywhere in the row.

I made an index match and also a xlookup but I keep getting a #SPILL error. I am assuming it can't read from all 6 cells and give the answer in one cell?

What options do I have? Thank you all in advance.

 

[Joe4]

I am having a hard time visualizing your data structure. Could you show us some sample data and your expected output?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Severno]

I am having a hard time visualizing your data structure. Could you show us some sample data and your expected output?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Absolutely! I'll work on that when I get home. Thank you.

 

[Severno]

Absolutely! I'll work on that when I get home. Thank you.

Route #	Stop 1	Stop 2	Stop 3	Stop 4	Stop 5	Stop 6	Delivery Window
10	Boston	NYC	LA	Detroit	Orlando	Charlotte	(I want LA - 10:00 - 12:00 to show here)
11	Charlotte	NYC	Boston	Portland	Detroit	Richmond	( I want Richmond - 6:00 - 8:00 to show here)
12	LA	NYC	Detroit	Portland	Orlando	Charlotte	(I want LA - 10:00 - 12:00 to show here)
13	Detroit	LA	Porland	Boston	NYC	Charlotte	(I want LA - 10:00 - 12:00 to show here)
14	Detroit	NYC	Richmond	Boston	Charlotte	Orlando	( I want Richmond - 6:00 - 8:00 to show here)
15	NYC	Richmond	Boston	Charlotte	Orlando	Detroit	( I want Richmond - 6:00 - 8:00 to show here)
			LA Delivery Window is 10:00 to 12:00
			Richmond Window is 6:00 to 8:00

 

[Severno]

Route #	Stop 1	Stop 2	Stop 3	Stop 4	Stop 5	Stop 6	Delivery Window
10	Boston	NYC	LA	Detroit	Orlando	Charlotte	(I want LA - 10:00 - 12:00 to show here)
11	Charlotte	NYC	Boston	Portland	Detroit	Richmond	( I want Richmond - 6:00 - 8:00 to show here)
12	LA	NYC	Detroit	Portland	Orlando	Charlotte	(I want LA - 10:00 - 12:00 to show here)
13	Detroit	LA	Porland	Boston	NYC	Charlotte	(I want LA - 10:00 - 12:00 to show here)
14	Detroit	NYC	Richmond	Boston	Charlotte	Orlando	( I want Richmond - 6:00 - 8:00 to show here)
15	NYC	Richmond	Boston	Charlotte	Orlando	Detroit	( I want Richmond - 6:00 - 8:00 to show here)
			LA Delivery Window is 10:00 to 12:00
			Richmond Window is 6:00 to 8:00

So picture having over 200 stores and nearly 25 routes a night. On any given route there will be up to one store that has a delivery window. I want it to read through stop one through 6 and identify if there is a special window and if there is show it in the last column. I have multiple sheets with the store numbers and names as well as the windows.

 

[Joe4]

OK, try this:

Formula to put in cell H2 and copy down for all rows:

=IF(COUNTIF(B2:G2,"LA")>0,"LA 10:00 - 12:00",IF(COUNTIF(B2:G2,"RICHMOND")>0,"Richmond 6:00 - 8:00",""))

 

[Severno]

OK, try this:
View attachment 108013

Formula to put in cell H2 and copy down for all rows:

=IF(COUNTIF(B2:G2,"LA")>0,"LA 10:00 - 12:00",IF(COUNTIF(B2:G2,"RICHMOND")>0,"Richmond 6:00 - 8:00",""))

OK, try this:
View attachment 108013

Formula to put in cell H2 and copy down for all rows:

=IF(COUNTIF(B2:G2,"LA")>0,"LA 10:00 - 12:00",IF(COUNTIF(B2:G2,"RICHMOND")>0,"Richmond 6:00 - 8:00",""))

This is going to work! Thank you very much.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Severno]

This is going to work! Thank you very much.

I am having trouble adding the second one in there. I got it to work using only one. I'll play with this and try and get it.

 

[Joe4]

What are you having issues with?
I already have both options already listed in the formula.

If it is not working as expected, it usually means that either:
- the range is not correct
- the text does not match exactly (look for extra spaces or special characters)