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

 

[SpillerBD]

It's a good tiny bit of code that gets the job done. Luckily Australian post codes have a numeric regularity, UK and Canadian.
The only addition the OP may want would be how values not in any Case range are handled. Currently returns blank if code is Invalid. I would think it may be better to return something more definitive.

If I was in need for my own desk such a function, I would likely stick with Peter's type of solution, unless I was in PowerQuery/Data Model.
Usually three decent ways to accomplish the same end, it just depends on end-use as to which ones rise to the top.

After selecting the function and finishing the edit, you will likely have, which references where the code for the function is stored.

=Personal.xlsb!State(F4)

foiled by this editor doh! Should have been , "unlike UK and Canada."

 

[Peter_SSs]

One of things that may have been overlooked with any named range or worksheet lookup suggestion, and one of the reasons I went for the UDF, is the the following comment from the OP in post #1 . I could be wrong but I took "ton of my spreadsheets" to mean "ton of my workbooks" rather than "ton of my worksheets". I'm not sure the named range or lookup formula suggestions would lend themselves so well to that.

.. it gets used in a ton of my spreadsheets..

BTW, unless every postcode in the country is listed in the named range, the following change would need to be made (and carefully constructing the lookup table to be in appropriate order)

Make a named Range State that uses your Formula as the Refences = Vlookup(A1,named_range,2,<del>0 </del>1)

The other reason I gave a UDF was that the OP appears to ask for that (though I readily admit OPs often ask for something specific, not knowing a better alternative may be available).

Anyway, hopefully we hear back from the OP again regarding the error that seems to be happening with the UDF.

 

[AceFI]

hi all. sorry for the late reply. this worked; =Personal.xlsb!State(F4)

 

[Peter_SSs]

hi all. sorry for the late reply. this worked; =Personal.xlsb!State(F4)

Good news! Whilst I mentioned moving the function to that workbook, I didn't add that detail & then didn't even twig that was causing the error you reported, sorry about that. Anyway, SpillerBD's picked up on it thankfully.