Someone can help me out with writing a simple function??

[crazyjji]

Hi folks~

I just started taking a vba class so I have a minimal knowledge on function writing.

Can someone help me to write function that has the following macro functions?

=HLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_array, [match type]), [range_lookup])


I use this a lot but it is too messy if I have to put these same function more than two in just one cell.

Thanks!!

[Bob Phillips]

For all matches

[vba]

Function MyLookup(lookup_value, hlookup_array, match_array)
Dim match_index As Long
Dim hlookup_index As Long
Dim cell As Range

Set cell = match_array.Find(lookup_value)
match_index = cell.Row - match_array.Cells(1, 1).Row + 1
Set cell = hlookup_array.Rows(1).Find(lookup_value)
hlookup_index = cell.Column - hlookup_array.Cells(1, 1).Column + 1
MyLookup = hlookup_array.Cells(match_index, hlookup_index).Value

End Function
[/vba]

[crazyjji]

lookup value for hlookup and lookup value for match are different~~~

For all matches

[vba]

Function MyLookup(lookup_value, hlookup_array, match_array)
Dim match_index As Long
Dim hlookup_index As Long
Dim cell As Range

Set cell = match_array.Find(lookup_value)
match_index = cell.Row - match_array.Cells(1, 1).Row + 1
Set cell = hlookup_array.Rows(1).Find(lookup_value)
hlookup_index = cell.Column - hlookup_array.Cells(1, 1).Column + 1
MyLookup = hlookup_array.Cells(match_index, hlookup_index).Value

End Function
[/vba]

[Bob Phillips]

No problem

[vba]

Function MyLookup(hlookup_value, hlookup_array, match_value, match_array)
Dim match_index As Long
Dim hlookup_index As Long
Dim cell As Range

Set cell = match_array.Find(match_value)
match_index = cell.Row - match_array.Cells(1, 1).Row + 1
Set cell = hlookup_array.Rows(1).Find(hlookup_value)
hlookup_index = cell.Column - hlookup_array.Cells(1, 1).Column + 1
MyLookup = hlookup_array.Cells(match_index, hlookup_index).Value

End Function
[/vba]