Multiple ranges in a formula

Russk68

Active Member
Joined
May 1, 2006
Messages
477
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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

AngelEG

Board Regular
Joined
Nov 17, 2015
Messages
61
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
 

Russk68

Active Member
Joined
May 1, 2006
Messages
477
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!
 

AngelEG

Board Regular
Joined
Nov 17, 2015
Messages
61
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)?
 

Russk68

Active Member
Joined
May 1, 2006
Messages
477

ADVERTISEMENT

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!
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
If you need to use/select from multiple ranges, maybe giving them named ranges will help?
 

Russk68

Active Member
Joined
May 1, 2006
Messages
477

ADVERTISEMENT

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!
 

Russk68

Active Member
Joined
May 1, 2006
Messages
477
Hi Ford
Holy crap!
Now that I have named the ranges I see that this should work!
 

AngelEG

Board Regular
Joined
Nov 17, 2015
Messages
61
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,946
Members
414,417
Latest member
Nobu

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
Top