Conceptually understanding how named ranges work with validation list

[ibvalentine]

I have a worksheet that contains four ranges consisting of a list of sales people and their total sales for each quarter of the year:

Tom $40,000
**** $44,000
Harry $47,000
Mary $51,000

Tom $40,000
**** $44,000
Harry $44,000
Mary $43,000

Tom $42,000
**** $44,000
Harry $49,000
Mary $43,000

Tom $49,000
**** $46,000
Harry $44,000
Mary $42,000

This is not an exact representation of the ranges (which include months) but this gives you an idea. I have two validation lists: one for the sales_people and another for the ranges of the totals sales for each quarter: Qtr1_Sales, Qtr2_Sales, Qtr3_Sales, and Qtr4_Sales.

The idea is to set up a double lookup table to quickly look up a sales person's sales for any particular quarter. For example, Tom's 3 quarter's sales were $42,000. Harry's second quarter sales were $45,000.

In E3, I have a lookup for the salesperson's name, in E4, I have a lookup for each Qtr, and in E5, I have my result. Here's my function (it works fine): INDEX(INDIRECT(E4), MATCH(E3, sales_people, 0))

This formula also works: INDEX(Qtr3_Sales, MATCH(E3, sales_people, 0))

So, I am wonderiing why this doesn't work: INDEX(E4, MATCH(E3, sales_people, 0))

If E4 contains Qtr3_Sales, why are not the results the same for the two previous formulas?

[FDibbins]

Hi and welcome to the forum

I would say (without actually seeing any sample data), index needs a range to look in...=index(range,row,column. Qtr3+Sales is a range (a named range with 1 or more cells), but E4 is a single cell, not a range

[ibvalentine]

Yes, I thought about the same thing. But the ranges are named and each of those names are in the dv list. So, again, I cannot understand why INDEX(E4, MATCH(E3, sales_people, 0)) is not the same as INDEX(Qtr3_Sales, MATCH(E3, sales_people, 0)) when E4 contains Qtr3_Sales. The second formula works (the one with Qtr3_Sales) but the first formula doesn't. Also, why would the INDIRECT function make a difference. (I am actually of the verge of making sense of it but not quite there yet.)

(I'm editing this post.) On further reflection, perhaps the value in E4 is just a reference to the range and not the actually range and the INDIRECT function represents the range itself. Does that make sense? I would welcome any other explanations.

[ibvalentine]

Okay, I think I have it figured out. When I created the dv list, I had to manually type the values in. Since I was typing the name of the ranges for each quarter, I assume that for formula would see these as named ranges, but I created text strings that did not directly reference the range. The INDIRECT function is needed to use each text string as a reference to find the named ranges. That makes sense to me.