Suppose I have a mapping of string to string as follows:

  • "orange" => "balloon"
  • "red" => "bicycle"
  • "blue" => "bird"

Is there a formula that can be constructed (say in B1) such that it will lookup the value from another cell (say A1) and dereference the string?

For example, if A1 is "red" then B1 would be "bicycle". If the color does not exist (e.g. "purple"), then I want to output a string like "NOT FOUND".

I need this to be completely in-line - in the formula itself, without writing the data to any cells/tables.

Also, prefer if it's not just one big If/Then/Else formula (which is my punt plan). Looking for something more terse.

  • How are you mapping these cells? How does it already know that "balloon" is actually "orange"? – K.Dᴀᴠɪs Feb 15 '18 at 16:54
  • It's a fixed mapping. – SFun28 Feb 15 '18 at 17:00
  • For an elegant inline solution using INDEX and MATCH you will need to have a data table that matches your strings together. If you don't want to display them perhaps you can create those tables in a named range or a hidden sheet. – wbeard52 Feb 16 '18 at 6:12

If you are okay with creating a UDF (User Defined Function) within VBA then you can do something along the lines of:

Function dereferenceCell(ByVal strReference As String) As String

    If strReference = vbNullString Then Exit Function

    Select Case strReference
        Case "orange"
            dereferenceCell = "balloon"
        Case "red"
            dereferenceCell = "bicycle"
        Case "blue"
            dereferenceCell = "bird"
        Case Else
            dereferenceCell = "NOT FOUND"
    End Select

End Function

Then in your B1 (or B2 with headers) cell, you can just use the formula =dereferenceCell(A2)

enter image description here

| improve this answer | |
  • @SFun28 No problems. Someone else may find it useful ;-) – K.Dᴀᴠɪs Feb 15 '18 at 17:05
=IFERROR(INDEX( {"balloon","bicycle","bird"}, MATCH("orange", {"red","blue"}, 0)),"NOT FOUND")
| improve this answer | |
  • Please note that IFNA was introduced in Excel 2013. Your question tags show Excel 2010 and Excel 2013, so this solution is a bit misleading. – davidmneedham Feb 15 '18 at 17:31
  • @davidmneedham - good catch. IFNA should be changed to IFERROR – SFun28 Feb 15 '18 at 17:34
  • Note that this solution should use a 0 as the last parameter of match for exact lookup – SFun28 Feb 15 '18 at 17:35
  • I suggest that you edit your answer to include the corrections that you have mentioned in the comments. – Blackwood Feb 16 '18 at 18:45
  • @Blackwood - done! I actually tried yesterday but couldn't edit it for some reason. I'm glad you reminded me to come back to this. – SFun28 Feb 16 '18 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.