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.

 

[Severno]

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)

I got it to work! Thank you again.

 

[Joe4]

I got it to work! Thank you again.

You are welcome.
Out of curiosity, what was the issue?

 

[Severno]

You are welcome.
Out of curiosity, what was the issue?

When I translated it back over to the actual data that I had I had an extra, in there where it wasn't supposed to be. It was completely my fault but I do appreciate everything and now it's working perfectly.

 

[Severno]

When I translated it back over to the actual data that I had I had an extra, in there where it wasn't supposed to be. It was completely my fault but I do appreciate everything and now it's working perfectly.

I have a question Joe4... Just in case there is a scenario where there are 2 delivery window in the same row is there a way to get both of them carried over?

 

[Joe4]

Maybe something like this?

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