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

 

[Peter_SSs]

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

The image doesn't really help much I'm afraid, as we can't also see the code. However, I guess it's unlikely you spelled such a simple word incorrectly in either place.

The formula in the active cell in your screen shot should actually be =State(F4) by my counting, though that should not be related to the #NAME ? error.

The only way I can make that error is to make the formula something like =StateF4
Again, unlikely you have done that.

What version of Excel & what Operating System are you using?

Can you upload a copy of the file (any sensitive information removed) to a file-share site (eg Dropbox), make sure it is shared & provide a link to the file?

 

[Dave Patton]

Ensure the UDF is in a regular Module not the sheet?

 

[Peter_SSs]

Should the UDF be in a regular Module not the sheet?

If the OP follows the instructions I gave, the UDF will be in a standard module.

 

[SpillerBD]

Why not use a "Named" range formula?

 

[mole999]

Why not use a "Named" range formula?

I think Peters 12 lines of code, far outweigh a named range in this instance

 

[SpillerBD]

I think Peters 12 lines of code, far outweigh a named range in this instance

? Really.
Select B1. Then go to the Name manager.
Make a named Range State that uses your Formula as the Refences = Vlookup(A1,named_range,2,0)
Then in the cell the value is desired to be returned =State
The Lookup value would be in the cell 1 to the left.

I think that's far less code.

I too often forget the ability to use a formula in a "Named" range to replace more complex formulas when I can.
One advantage is you do not have to deal with macro security issues.

 

[mole999]

? Really.
Select B1. Then go to the Name manager.
Make a named Range State that uses your Formula as the Refences = Vlookup(A1,named_range,2,0)
Then in the cell the value is desired to be returned =State
The Lookup value would be in the cell 1 to the left.

I think that's far less code.

I too often forget the ability to use a formula in a "Named" range to replace more complex formulas when I can.
One advantage is you do not have to deal with macro security issues.

Thank you for repeating exactly what I said in Post 2 above. Though having looked at the required complexity I still would go with 12 lines of code

 

[SpillerBD]

Thank you for repeating exactly what I said in Post 2 above. Though having looked at the required complexity I still would go with 12 lines of code

no, not the same. Post #2 indicates the formula in each cell.
I suggest the formula in the Named Range, reducing the formula in each cell to

=State

 

[SpillerBD]

If the OP follows the instructions I gave, the UDF will be in a standard module.

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)

 

[mole999]

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)

It wouldn't work with UK PostCodes, far to much variability (about 50 properties average each)