Is there an Excel macro or VB function to do this?

Factual Questions
1

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.

2

I can think of a way to do this without a macro or VB function, is that a necessity?

3

I know it’s detailed in the online Help how to do this. I just want something that’s a lot more readable.

4

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.

5

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):



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. :slight_smile:

6

CWG, did you mix-up ARRAYVALUE and ARRAYINDEX?

7

Sure looks that way :smack: Pick one or the other and use it consistently.

8

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.

9

That there might be multiple arrays on the same sheet and that creating names for each is a real pain.

Star man! That’s vastly nicer than the hugely complex function they give in the help. I’ll give that a whirl tomorrow.

10

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.

11

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.

12

Absolutely correct, however, if you already know all of that information you might as well just type

=J12

13

Cooking, you are right. I was just answering the OP’s quest, viz:

Best to all,

w.

14

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.