Custom vlookup function?

A

AceFI

Board Regular
Joined
Apr 20, 2012
Messages
91
hi all.
I'm wanting to create my own formula function, but not too sure where to begin..

basic; =WhichState(A1)


3931VIC
4000QLD
6000WA
2011NSW

<tbody>
</tbody>

at the moment i'm using a =left, plus a vlookup and its a bit messy, and it gets used in a ton of my spreadsheets..

any tips for getting started?

TIA
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
built in functions are more efficient

If you have a list of numbers and associated states you can create a named_range for them, then

=Vlookup(A1,named_range,2,0)

also the new function might be volatile requiring many recalculations of your values
 
Upvote 0
If you have a defined set of code to lookup you could do it with a function like this , which will be much faster than using Vlookup or index match because it doesn't access any other worksheet, so it will be much more efficient that vlookup
Code: 
Public inarr(1 To 5, 1 To 2) As Variant



Function test(inpt)
inarr(1, 1) = "A"
inarr(2, 1) = "B"
inarr(3, 1) = "C"
inarr(4, 1) = "d"
inarr(5, 1) = "E"
inarr(1, 2) = "A1"
inarr(2, 2) = "B2"
inarr(3, 2) = "C3"
inarr(4, 2) = "d4"
inarr(5, 2) = "E5"
test = ""
For i = 1 To 5
 If inpt = inarr(i, 1) Then
    test = inarr(i, 2)
    Exit For
 End If
Next i


End Function
 
Upvote 0
Try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

If you want to use this in multiple workbooks, move the code to a standard module in your PERSONAL.XLSB workbook

Code: 
Function State(s As String) As String
  Select Case Val(s)
    Case 1000 To 2599, 2619 To 2899, 2921 To 2999: State = "NSW"
    Case 200 To 299, 2600 To 2618: State = "ACT"
    Case 3000 To 3999, 8000 To 8999: State = "VIC"
    Case 4000 To 4999, 9000 To 9999: State = "QLD"
    Case 5000 To 5999: State = "SA"
    Case 6000 To 6999: State = "WA"
    Case 7000 To 7999: State = "TAS"
    Case 800 To 999: State = "NT"
  End Select
End Function

Excel Workbook
AB
1PostcodeState
24864QLD
35276SA
44770QLD
55860SA
62480NSW
75761SA
85004SA
98585VIC
100298ACT
110864NT
128638VIC
139406QLD
145348SA
150930NT
166023WA
177146TAS
State From Postcode
 
Last edited:
Upvote 0
thanks Peter,
i'm gettin a #name ? error on this?
also, is there a way to make the source an integer if it is not already?

thanks
 
Upvote 0
AceFI said:
i'm gettin a #name ? error on this?
Click to expand...
That would normally indicate that the spelling of your function is not consistent in the code & worksheet.
Peter_SSs said:
Rich (BB code): 
Function State(s As String) As String
  Select Case Val(s)
    Case 1000 To 2599, 2619 To 2899, 2921 To 2999: State = "NSW"
    Case 200 To 299, 2600 To 2618: State = "ACT"
    Case 3000 To 3999, 8000 To 8999: State = "VIC"
    Case 4000 To 4999, 9000 To 9999: State = "QLD"
    Case 5000 To 5999: State = "SA"
    Case 6000 To 6999: State = "WA"
    Case 7000 To 7999: State = "TAS"
    Case 800 To 999: State = "NT"
  End Select
End Function

and in the worksheet:

=State(A2)
Click to expand...




AceFI said:
also, is there a way to make the source an integer if it is not already?
Click to expand...
Could you give some example of that type of data, and the corresponding expected results?
 
Upvote 0
the import file is a CSV... previously when using the vlookup method, i had to have a separate col. again =int(etc)
i've attached a screenshot of the output
Screen%20Shot%202018-04-17%20at%205.07.47%20pm.png
 
Upvote 0
shouldn't that be

=State(F3)

and not sure if it matters the function starts with UPPERCASE and you have a LOWERCASE letter
 
Last edited:
Upvote 0
it is =State(F3) (can't didn't make a difference) i screenshot the way i did to show that the custom function was visible to to the spreadsheet
 
Upvote 0
if you want to see the cell formulas (on a normal keyboard this works)

CTRL+ the key left of 1, has three symbols on it (no idea how to reference it otherwise) :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

dezibluenose
  • Solved
IF combined with VLOOKUP throwing up the "too many arguments for this function" reply
Replies
8
Views
553
Alex Blakenburg
A
R
  • Question
3D dynamic lookup with duplicates across multiple sheets
Replies
0
Views
1K
Rich N
R
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
M
  • Question
Fantasy Premier League (FPL) - Swapping players for subs depending on minutes played
Replies
2
Views
659
MaccyW
M
N
  • Question
Nesting other functions with vlookup
Replies
4
Views
486
Noobllianz
N

Forum statistics

Threads
1,215,045
Messages
6,122,836
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top