G. Nelson


I am putting together a small function to act as a simultaneous VLOOKUP and HLOOKUP, but when I try to pass the range as an argument, I get an error telling me that the range I'm passing is the wrong type ....

this is my code
Function ARRAY_LOOKUP(column_value As Variant, row_value As Variant, array_area As Range) As Variant
For i = 1 To array_area.Width
If array_area.Cells(1, i) = column_value Then
col_num = i
End If
Next i
For i = 1 To array_area.Height
If array_area.Cells(i, 1) = row_value Then
row_num = i
End If
Next i
ARRAY_LOOKUP = array_area.Cells(row_num, col_num)
End Function


It's being called in the cell as
=ARRAY_LOOKUP("A","B",A1:E7)

Obviously, I'm doing something wrong, but I can't see it.




Re: Problem with passing a Range as an argument to a function

Andy Pope


Hi,

I did not get the same error as you but you do have the wrong properties Width/Height.
You need to use the Count of rows and columns.

Code Snippet

Function ARRAY_LOOKUP(column_value As Variant, row_value As Variant, array_area As Range) As Variant
Dim i
Dim col_num
Dim row_num

For i = 1 To array_area.Columns.Count
If array_area.Cells(1, i) = column_value Then
col_num = i
End If
Next i
For i = 1 To array_area.Rows.Count
If array_area.Cells(i, 1) = row_value Then
row_num = i
End If
Next i
ARRAY_LOOKUP = array_area.Cells(row_num, col_num)
End Function

Of course you could just use a formula.


=IF(OR(ISERROR(MATCH("A",B1:E1,0)),ISERROR(MATCH("B",A2:A7))),NA(),INDEX(B2:E7,MATCH("B",A2:A7,0),MATCH("A",B1:E1,0)))