Looking for Feedback or Previous Experience on My Article on Scripting.Dictionary


Board Regular
Mar 9, 2010
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”).


  1. The before value is case-sensitive when used in the spreadsheet in a formula, i.e. =retrieve(
  2. The before and after values must both be in quotes.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...