CODEWITHSUNDEEP
excelvba
Tue Larsson
Tue Larsson

Reputation: 21

Simple custom function in Excel, using Vlookup

I want to have a faster process looking up cross references.

Right now I use VLOOKUP, and it works fine - but it takes time when it needs to be done multiple times everyday.

It is always the same sheet I use to lookup the cross references, so the only thing that changes is my input value in the VLOOKUP function.

Therefore I want a function where I only input 1 value to get the VLOOKUP value.

The idea is a function like:

=CROSS(ID)

where 

CROSS = vlookup(ID, table_array, col_index_num,[range_lookup])

So the vlookup_value is replaced by ID.

I hope you can provide me with some answers - thanks in advance.

I have tried multiple different things, but with no success. As I am new, I've googled and recorded macros to look for answers.

Upvotes: 1

Views: 1216

Answers (2)

Tue Larsson
Tue Larsson

Reputation: 21

I got it working as it should now!

The code ended up like this:

Sub crossref()

Option Explicit

Public Function CROSS(ID As Variant) As Variant
CROSS = Application.WorksheetFunction.VLookup(ID, Worksheets("Sheet1").Range("E:F"), 2, 0)

End Function

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57693

You could write a UDF (user defined function) for that, using the WorksheetFunction.VLookup method:

Option Explicit

Public Function CROSS(ID As Variant) As Variant

    CROSS = Application.WorksheetFunction.VLookup(ID, table_array, col_index_num, range_lookup)

End Function

Upvotes: 1

Related Questions