Returning a cell with from a range a data

S

Severno

New Member
Joined
Feb 21, 2024
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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.
 
Upvote 0
Joe4 said:
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.
Click to expand...
Absolutely! I'll work on that when I get home. Thank you.
 
Upvote 0
Severno said:
Absolutely! I'll work on that when I get home. Thank you.
Click to expand...
Route #Stop 1Stop 2Stop 3Stop 4Stop 5Stop 6Delivery Window
10BostonNYCLADetroitOrlandoCharlotte(I want LA - 10:00 - 12:00 to show here)
11CharlotteNYCBostonPortlandDetroitRichmond( I want Richmond - 6:00 - 8:00 to show here)
12LANYCDetroitPortlandOrlandoCharlotte(I want LA - 10:00 - 12:00 to show here)
13DetroitLAPorlandBostonNYCCharlotte(I want LA - 10:00 - 12:00 to show here)
14DetroitNYCRichmondBostonCharlotteOrlando( I want Richmond - 6:00 - 8:00 to show here)
15NYCRichmondBostonCharlotteOrlandoDetroit( 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
 
Upvote 0
Severno said:
Route #Stop 1Stop 2Stop 3Stop 4Stop 5Stop 6Delivery Window
10BostonNYCLADetroitOrlandoCharlotte(I want LA - 10:00 - 12:00 to show here)
11CharlotteNYCBostonPortlandDetroitRichmond( I want Richmond - 6:00 - 8:00 to show here)
12LANYCDetroitPortlandOrlandoCharlotte(I want LA - 10:00 - 12:00 to show here)
13DetroitLAPorlandBostonNYCCharlotte(I want LA - 10:00 - 12:00 to show here)
14DetroitNYCRichmondBostonCharlotteOrlando( I want Richmond - 6:00 - 8:00 to show here)
15NYCRichmondBostonCharlotteOrlandoDetroit( 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
Click to expand...
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.
 
Upvote 0
OK, try this:
1709814159212.png


Formula to put in cell H2 and copy down for all rows:
Excel Formula: 
=IF(COUNTIF(B2:G2,"LA")>0,"LA 10:00 - 12:00",IF(COUNTIF(B2:G2,"RICHMOND")>0,"Richmond 6:00 - 8:00",""))
 
Upvote 0
Joe4 said:
OK, try this:
View attachment 108013

Formula to put in cell H2 and copy down for all rows:
Excel Formula: 
=IF(COUNTIF(B2:G2,"LA")>0,"LA 10:00 - 12:00",IF(COUNTIF(B2:G2,"RICHMOND")>0,"Richmond 6:00 - 8:00",""))
Click to expand...

Joe4 said:
OK, try this:
View attachment 108013

Formula to put in cell H2 and copy down for all rows:
Excel Formula: 
=IF(COUNTIF(B2:G2,"LA")>0,"LA 10:00 - 12:00",IF(COUNTIF(B2:G2,"RICHMOND")>0,"Richmond 6:00 - 8:00",""))
Click to expand...
This is going to work! Thank you very much.
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Formula to aggregate emails if values in other cells match certain text
Replies
3
Views
505
Marcelo Branco
M
P
  • Question Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
604
purekarting
P
sanantonio
  • Solved
Returning next Date once Date has passed
Replies
5
Views
494
dreid1011
dreid1011
P
  • Solved
Filtering text data based on unique names and then pasting all the data into one single cell for each name
Replies
4
Views
161
p49
P
H
  • Solved
Excel VBA file importing data from several files located in same folder -> challenge to copy data only (rest is working perfectly)
Replies
3
Views
429
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top