Using Named Ranges in an Idex/Match Function

[chuckmichael]

Hello!

I need some expertise in revising the following formula:

=IFERROR(IF($I$2>=$F4,INDEX(PMR_JAN,MATCH($D4,INDEX(PMR_JAN,0,2),0),40),""),"")

I use this formula to look on another worksheet within data I've labeled "PMR_JAN". It looks in column 2, and returns the value in column 40. I use this rather than a vlookup because it would be inconvenient for me to have the data starting in the first column for other reporting purposes. It works perfectly for my needs.

What I'd LIKE to do is rather than use a column number, I'd like to give the column a name. For instance, I want to call the data in column 2 "Client" and the data in column 40 "Total".

So I can use these named ranges, rather than a number to identify the column to return. In the future, there may be a need to insert columns in the data table, and I'd like to avoid editing all the formulas like this, and by naming the column, it will always know where to look.

Is this even possible?

Thanks!

Chuck

 

[skywriter]

Yes you could do it, but there's a couple of things to be aware of.

You would give the column a name, let's just say you name the column "ResultColumn"

In your formula where you have 40 you would have it read Column(ResultColumn), which would return the number 40 to your column argument in the index function assuming ResultColumn refers to column AN.

What this will do is return the number of the column, however this will only work for the data you are indexing if the data starts in column A.

If you think about it, let's just say you were indexing data from Col C to Col F and your ResultColumn is actually column F. The Column(ResultColumn) would return the number 6, which would not work because you are indexing Col C to ColF and therefore the 6 would not work because there are only 4 columns in the range you are indexing, so unless the data you were indexing was starting in Column A the 6 won't work. So what you would do is if the data you are indexing does not start in Col A is subtract the Column Number of the ResultColumn from the Column Number just before the column you are indexing, so in the example I gave you. Column(ResultColumn)-Column(B:B) which would return 4 to the function resulting in the correct column number for what is being indexed.

Hope my explanation works for you.

 

[skywriter]

MAKE SURE YOU READ THE POST ABOVE OR THIS WON'T MAKE ANY SENSE.

I just thought of something, you could use COLUMN(ResultColumn)-COLUMN(PMR_JAN)+1 and that should give you the correct results. Since PMR_JAN is the range you are indexing COLUMN(PMR_JAN) will return the column number of the first column in the index range so subtracting that from ResultColumn and adding back 1 will give you the correct column number.

 

[chuckmichael]

Bruce - thanks, that worked perfectly. My named data does start in column A, so I used the first option you described.

I have a second, more thought provoking task now.

Using the same named range "PMR_JAN". I want to look in the "SignDate" range column, and have it return the "Client Number" range column, but ONLY if the date in "SignDate" is between 01/16/2014 and 02/15/2014. If the date doesn't fall into that range, I want to ignore it.

And, within that same named range, there will multiple values that match the above criteria. I want to copy this formula down about 5 rows, and have it return each unique value (there will only be about 5 instances).

Thoughts?

 

[skywriter]

Without sample data and knowing exactly where and what you want the result to be it's hard to just give an answer.