Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem with indirect for multi range


Hello, is there a way to use multiple ranges in function indirect?
How to give the ranges 1, 2, 3 and 4 to function indirect

In other words, I have these range





Each of which is located in a different cell

And if I give each of these cells to the function 3, all the numbers will return that range

But how can I return all the numbers in these 4 ranges



Selected Answer

Hi Kar2rost and welcome to the Forum.

This answer ahs been revised now that you have attached a file (as an Answer- it should be part of your question really). Usrs should note that you use right to left columns.

Your question is actually about using a complex formula to return the highest ranked number (without duplicates) but for multiple ranges. In J33, your formula for a single range is:


I suggest you use LARGE anstead, combined with COUNTIF to eliminate duplicates.

In the attached revised file, cell N38 (green)  has the range containing all values (and headings- which LARGE will ignore):


In cell K3, we get the highest value (ranked 1st) from that range using:


If we changed the second argument (k, the rank position) from 1 to a 2, we'd get the same again (since that value is duplicated). Instead, we count how many duplicates of K33 are in the range then add 1. The second argument becomes (in bold):


then copy that down and all duplicates will be ignored.You can either remove them from your range (e.g put them in anopther column) or adjust the formula in K33 to ignore them i.e.


Hope this works for you (please remember to mark this answer as Selected if so)


Answer the Question

You must create an account to use the forum. Create an Account or Login