# Is there an Excel macro or VB function to do this? **URL:** \ **Category:** Factual Questions\ **Created:** [July 16, 2008, 10:38pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759 "2008-07-16T22:38:03Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Quartz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quartz/32/267_2.png) [@Quartz](https://boards.straightdope.com/u/Quartz)\ **Post date:** [July 16, 2008, 10:38pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/1 "2008-07-16T22:38:03Z")
I have a range and want to find the intersection of the column name and row name. Something like =ARRAYVALUE(“Employee Data”, “Weekly Salary”, “Joe Bloggs”) where ARRAYVALUE is the name of the VB function, Employee Data is the name of the range, “Weeky Salary” the name of the column, and “Joe Bloggs” is a row. It would give me how much Joe Bloggs earns per week from the range (e.g. $5000). An example of how to do this is given in the help, but it’s rather complex and not very readable, so I’m wondering if someone has written a VB function to simplify it all up in the manner of the example above. ---
**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\ **Post date:** [July 16, 2008, 10:47pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/2 "2008-07-16T22:47:30Z")
I can think of a way to do this without a macro or VB function, is that a necessity? ---
**Author:** ![Quartz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quartz/32/267_2.png) [@Quartz](https://boards.straightdope.com/u/Quartz)\ **Post date:** [July 17, 2008, 6:31am UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/3 "2008-07-17T06:31:04Z")
I know it’s detailed in the online Help how to do this. I just want something that’s a lot more readable. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [July 17, 2008, 2:57pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/4 "2008-07-17T14:57:21Z")
It’s certainly possible to do this with a VBA function, although do you just need to use this in a particular workbook, or do you want it to be accessible from multiple workbooks? The former is easier. The latter requires either a fully qualified reference in the formula, something like (but probably not exactly like, I’d have to look it up): =[Macro workbook.xls]ARRAYVALUE(“Employee Data”, “Weekly Salary”, “Joe Bloggs”) or creating a .XLA file and having each user load it. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [July 17, 2008, 3:38pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/5 "2008-07-17T15:38:42Z")
Here is the VBA code to do this within the same workbook. It uses the same solution that others have probably noted but encapsulates it in VBA. Two things to note: 1. This code must be placed in a module. That is true for any user-defined function to work. 2. The first argument is not a string giving the name of the range, it’s the name of the range itself. So in your example above, you would instead omit the quotes like this: =ARRAYVALUE(Employee Data, “Weekly Salary”, “Joe Bloggs”) **On edit I note that Employee Data is an illegal range name because it contains a space; you would have to use something like EmployeeData.** Here is the code, I have tested this. It will work no matter where your range is (that is, it does not have to start in A1): ```auto Option Explicit Public Function ARRAYINDEX(indexRange As Range, columnValue As Variant, rowValue As Variant) As Variant Dim row_num As Long Dim column_num As Long With indexRange.Cells(1, 1) row_num = WorksheetFunction.Match(rowValue, .EntireColumn, 0) - .Row + 1 column_num = WorksheetFunction.Match(columnValue, .EntireRow, 0) - .Column + 1 ARRAYINDEX = WorksheetFunction.INDEX(indexRange, row_num, column_num) End With End Function ``` If you are not familiar with VBA or creating user-defined functions, write back. 🙂 ---
**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\ **Post date:** [July 17, 2008, 4:02pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/6 "2008-07-17T16:02:01Z")
CWG, did you mix-up ARRAYVALUE and ARRAYINDEX? ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [July 17, 2008, 4:10pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/7 "2008-07-17T16:10:29Z")
[QUOTE=Gangster Octopus] CWG, did you mix-up ARRAYVALUE and ARRAYINDEX? [/QUOTE] Sure looks that way :smack: Pick one or the other and use it consistently. ---
**Author:** ![intention](https://avatars.discourse-cdn.com/v4/letter/i/e95f7d/32.png) [@intention](https://boards.straightdope.com/u/intention)\ **Post date:** [July 17, 2008, 8:52pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/8 "2008-07-17T20:52:26Z")
Not sure why you’d want to use a macro or a function. Just name the ranges, you can use named rows and named columns instead. For example, if your row name is “John\_Smith”, and the column name is “Weekly\_Wages”, you can just say =John\_Smith Weekly\_Wages and it will give you the value found at the intersection of the two named ranges. What am I missing here? w. ---
**Author:** ![Quartz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quartz/32/267_2.png) [@Quartz](https://boards.straightdope.com/u/Quartz)\ **Post date:** [July 17, 2008, 9:48pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/9 "2008-07-17T21:48:07Z")
That there might be multiple arrays on the same sheet and that creating names for each is a real pain. > [@CookingWithGas](#): > > Here is the VBA code to do this within the same workbook. Star man! That’s vastly nicer than the hugely complex function they give in the help. I’ll give that a whirl tomorrow. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [July 17, 2008, 10:27pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/10 "2008-07-17T22:27:27Z")
[QUOTE=intention] Not sure why you’d want to use a macro or a function. Just name the ranges, you can use named rows and named columns instead. For example, if your row name is “John\_Smith”, and the column name is “Weekly\_Wages”, you can just say =John\_Smith Weekly\_Wages and it will give you the value found at the intersection of the two named ranges. What am I missing here? w. [/QUOTE] That works fine if you want to hardcode the formulas. But the way this function would really be used is probably not =ARRAYVALUE(EmployeeData, “Weekly Salary”, “Joe Bloggs”) but more likely something like =ARRAYVALUE(EmployeeData, A4, A5) where you need to figure this out dynamically based on other data. ---
**Author:** ![intention](https://avatars.discourse-cdn.com/v4/letter/i/e95f7d/32.png) [@intention](https://boards.straightdope.com/u/intention)\ **Post date:** [July 18, 2008, 1:22am UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/11 "2008-07-18T01:22:11Z")
CookingWithGas, thanks for your comment. In fact, you can do this without naming the range. Try something like =I12:M12 J4:J21 (note the space between the two ranges I12:M12 and J4:J21) It will give the value of the cell at the intersection of the two ranges. w. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [July 18, 2008, 10:44pm UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/12 "2008-07-18T22:44:34Z")
[QUOTE=intention] CookingWithGas, thanks for your comment. In fact, you can do this without naming the range. Try something like =I12:M12 J4:J21 (note the space between the two ranges I12:M12 and J4:J21) It will give the value of the cell at the intersection of the two ranges. w. [/QUOTE] Absolutely correct, however, if you already know all of that information you might as well just type =J12 ---
**Author:** ![intention](https://avatars.discourse-cdn.com/v4/letter/i/e95f7d/32.png) [@intention](https://boards.straightdope.com/u/intention)\ **Post date:** [July 25, 2008, 3:13am UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/13 "2008-07-25T03:13:12Z")
[QUOTE=CookingWithGas] Absolutely correct, however, if you already know all of that information you might as well just type =J12 [/QUOTE] Cooking, you are right. I was just answering the OP’s quest, viz: > [@](#): > > I have a range and want to find the intersection of the column name and row name. Best to all, w. ---
**Author:** ![Quartz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quartz/32/267_2.png) [@Quartz](https://boards.straightdope.com/u/Quartz)\ **Post date:** [July 25, 2008, 7:54am UTC](https://boards.straightdope.com/t/is-there-an-excel-macro-or-vb-function-to-do-this/456759/14 "2008-07-25T07:54:37Z")
Thanks to all. As I noted in my OP, the problem was not ‘how to do it exactly’, because they already detail that, but ‘how to do it legibly’, and **CookingWithGas** ’s function does it admirably.