Custom vlookup function?

[AceFI]

hi all.
I'm wanting to create my own formula function, but not too sure where to begin..

basic; =WhichState(A1)


[TABLE="width: 500"]
<tbody>[TR]
[TD]3931[/TD]
[TD]VIC[/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]QLD[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]WA[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]NSW[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[mole999]

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

 

[offthelip]

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

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

 

[Peter_SSs]

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

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
	A	B
1	Postcode	State
2	4864	QLD
3	5276	SA
4	4770	QLD
5	5860	SA
6	2480	NSW
7	5761	SA
8	5004	SA
9	8585	VIC
10	0298	ACT
11	0864	NT
12	8638	VIC
13	9406	QLD
14	5348	SA
15	0930	NT
16	6023	WA
17	7146	TAS
State From Postcode

 

[AceFI]

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

 

[Peter_SSs]

i'm gettin a #name ? error on this?

That would normally indicate that the spelling of your function is not consistent in the code & worksheet.

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)

also, is there a way to make the source an integer if it is not already?

Could you give some example of that type of data, and the corresponding expected results?

 

[AceFI]

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

 

[mole999]

shouldn't that be

=State(F3)

and not sure if it matters the function starts with UPPERCASE and you have a LOWERCASE letter

 

[AceFI]

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

 

[mole999]

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)