A Lookup Function Across Spreadsheets
How did this function come to be and what does it do?
I was working on a project comparing the statistics of states in pivot tables. Most of the information I found used state names, but for pivot tables I wanted to use their abbreviations. I got tired of using VLOOKUP whenever I needed to switch a state name to its abbreviation or back again. That’s where this custom function came in.
This is an example of what people came up with. It works like a VLOOKUP, but is available across all spreadsheets. It can be text, number or dates.
I have not been able to add more than 3,000 lines per function. I have been able to several functions of 3,000 lines.
Function Retrieve(ByVal str As String) As String
With CreateObject("Scripting.Dictionary")
.Add "AL", "Alabama"
.Add "AK", "Alaska"
.Add "Alabama-P","4,875,000"
Add “adate”,”01/01/2018”
'Although population is shown in quotes, it comes into Excel
‘as a number
If .exists(str) Then Retrieve = .Item(str)
End With
End Function
The function can refer to a cell as in RETRIEVE(B1) or may be hard-coded, i.e. RETRIEVE(“AL”).
Notes:
How did this function come to be and what does it do?
I was working on a project comparing the statistics of states in pivot tables. Most of the information I found used state names, but for pivot tables I wanted to use their abbreviations. I got tired of using VLOOKUP whenever I needed to switch a state name to its abbreviation or back again. That’s where this custom function came in.
This is an example of what people came up with. It works like a VLOOKUP, but is available across all spreadsheets. It can be text, number or dates.
I have not been able to add more than 3,000 lines per function. I have been able to several functions of 3,000 lines.
Function Retrieve(ByVal str As String) As String
With CreateObject("Scripting.Dictionary")
.Add "AL", "Alabama"
.Add "AK", "Alaska"
.Add "Alabama-P","4,875,000"
Add “adate”,”01/01/2018”
'Although population is shown in quotes, it comes into Excel
‘as a number
If .exists(str) Then Retrieve = .Item(str)
End With
End Function
The function can refer to a cell as in RETRIEVE(B1) or may be hard-coded, i.e. RETRIEVE(“AL”).
Notes:
- The before value is case-sensitive when used in the spreadsheet in a formula, i.e. =retrieve(
- The before and after values must both be in quotes.
