Named ranges for table array, and Column index number?

**phefray** · 08-15-2012, 07:49 PM

I have been trying to use named ranges for both the table array and column index numbers in a vlookup, but have been unsuccessful.

This works:
=VLOOKUP(Table2[[#This Row],[Acct]],Table1,2,0)

This doesn't:
=VLOOKUP(Table2[[#This Row],[Acct]],Table1,[Weekday],0)

Nor this:
=VLOOKUP(Table2[[#This Row],[Acct]],Table1,Weekday,0)

Neither does this:
=VLOOKUP(Table2[[#This Row],[Acct]],Table1,Table1[Weekday],0)

The goal is to use common field names so anyone reading the formula knows what it is doing.

Is this possible to use more than one range name? Or perhaps the "range within a range" is just too much for it to understand?

**andrewhharmon** · 08-16-2012, 05:49 PM

can you describe or provide a sample of Table1 and Table2?

**phefray** · 08-16-2012, 05:55 PM

Andrew,
Unfortunately, I cannot provide a sample of these tables as they were originally. They were a fake data set of gobblety gook with no meaning. I did not save the file. It is easily reproduced with any other set of data. It was all made up just to test the concept.

**andrewhharmon** · 08-16-2012, 06:10 PM

Well, what I think you are trying to do is return the column number of [weekday]. Assuming weekday is a header in Table1 and lets say it's the 3rd column... using a MATCH formula will return the column number. MATCH("Weekday",Table1[#Headers],0). This will return the value 3, which is what the VLOOKUP is asking for. What's happening there is the match formula is looking in the range of the table header row and looking for the workd weekday. When, it finds it, it returns the column it was in. "0" is used to indicate it needs to be an exact match.

So i think this should work.

Please Login or Register  to view this content.

**phefray** · 08-16-2012, 06:43 PM

Andrew,
Thank you for the reply. I understand about the MATCH Function. It just seemed to me that it would be much more readable if the column index were a named range also. MATCH works great, but can cloud the intent if someone is reading it like a sentence. I don't think it is possible.