Multiple ranges in a formula

Russk68

Well-known Member
Joined
May 1, 2006
Messages
589
Office Version
  1. 365
Platform
  1. MacOS
Hello all!
I have a formula below that search a range (A5:A1000) (C5:N1000) (A5:A1000) I discovered that I need to add 24 separate ranges for each range and I need help on how to write it in.

Existing formula
=IF(A3="","",INDEX(Distro!$A$5:$A$1000,SUMPRODUCT(--(Distro!$C$5:$N$1000=A3)*(ROW(Distro!$A$5:$A$1000)-4))))

I need to add 24 ranges for each of the 3 ranges. Example below:
(A5:A40) (C5:C40) (A5:A40)
(A45:A80) (C45:C80) (A45:A80)
(A85:A125) (C85:C125) (A85:A125) and so on...

And the same here just for this part (Distro!$C$5:$N$1000=A3)
=IF(A3="","",INDEX(Distro!$C$4:$N$4,SUMPRODUCT(--(Distro!$C$5:$N$1000=A3)*(COLUMN(Distro!$C$5:$N$5)-2))))
(C5:N40)
(C45:N80)
(C85:C125) and so on:

I would greatly appreciate it if someone could give me an example on adding in 2 ranges and then I could figure out the rest.

Greatly appreciated!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Not sure I understand what do you mean about adding the 24 ranges on the 3 ranges... ???

Have you tried OFFSET, you can actually use it to return a range OFFSET(ref cell, rows, cols, [height], [width]) in you case the height can be used...

But again, it's a shot in the dark, if you can further explain about the 24 ranges in the 3 ranges I might be able to help.

cheers,

Angel
 
Upvote 0
Thank you very much for replying! i'll try to explain better.

The first formula =IF(A3="","",INDEX(Distro!$A$5:$A$1000,SUMPRODUCT(--(Distro!$C$5:$N$1000=A3)*(ROW(Distro!$A$5:$A$1000)-4)))) looks at my Distro! sheet from A5-A1000. My Distro! sheet is 24 pages and each page is 40 rows. The first 4 rows of each page has header information in it which returns errors because the formula is looking at all 24 pages. So for this formula to work properly, I need it to look at (1 page at a time) A5:A40 then A45:A80 then A85:A120 and so on to search the 25 pages. This would be true for all 3 arguments.
I am fairly new to Excel so I will study up on OFFSET.
Does this make sense?

Thank you very much!
 
Upvote 0
Ok, so you are looking to search for a value A3 in the Distro sheet in the range C5:N1000 and return the value in A5:A1000

Question, what type of error are you getting? your formula should work as it is, can you post a small sample of your header and data and also the sample of what your searching for (value of A3)?
 
Upvote 0
The formula works as it should but it's picking up information in rows that are not wanted. I attached a snapshot. As you can see in rows 1-4 there are arbitrary numbers in the header that give me undesired results.

Since I don't know how to attach a file, you can see a snapshot at the link below.
https://www.dropbox.com/s/3e5lj7v8ln43zg0/1Distro.png?dl=0

Thank you!
 
Upvote 0
If you need to use/select from multiple ranges, maybe giving them named ranges will help?
 
Upvote 0
Hmmm. I like the direction this is going. I see how named ranges would simplify things. I would just need to figure out how to put multiple named ranges in the formula.

Thanks for the suggestion!
 
Upvote 0
Hi Ford
Holy crap!
Now that I have named the ranges I see that this should work!
 
Upvote 0
Another work around without modifying the formula would be to have the numbers in your header defined as text, that way the formula will not identify them as numerical values and skips over when looking for your data.

You can easily do this by typing an apostrophe ' in front of the number e.g. '2 is used as text 2 instead of number 2

I've been playing with your formula and a limitation it has is that if you have 2 equal values in the search data it will not work, just a heads up in case you encounter this problem in the future.

Angel
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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