<aside> <img src="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/a5a1e3ce-00fa-46e4-9a75-58c2aed9e8ce/Notion_Fundamentals_with_Thomas_Frank_-_Avatar_2021.png" alt="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/a5a1e3ce-00fa-46e4-9a75-58c2aed9e8ce/Notion_Fundamentals_with_Thomas_Frank_-_Avatar_2021.png" width="40px" />

This is a proof-of-concept build that uses multiple formulas, relations, and rollups to recreate the VLOOKUP function from Excel. Create a row in the Lookup table, type a state from the Data table, and you’ll get its capital in the Smart Match property.

Created by Thomas Frank | Learn Notion Formulas | Notion Basics | Templates | Twitter

</aside>

Lookup

Untitled

Data

Data

“Pair” Property:

"/" + prop("State") + " - " + prop("Capital") + "/"

“Smart Match” Property:

// Compressed
empty(prop("Search")) ? "" : replace(replace(prop("Ag Full Roll"), ".*/(" + prop("Search") + "[^/]+).*", "$1"), ".+-\\\\s(\\\\w+)", "$1")

// Expanded
empty(
    prop("Search")
) ? 
"" : 
replace(
    replace(
        prop("Ag Full Roll"), 
        ".*/(" + prop("Search") + "[^/]+).*", 
        "$1"
    ), 
    ".+-\\\\s(\\\\w+)", 
    "$1"
)