---
title: "Smartsheet Nested IF Alternative: INDEX MATCH Lookup Tables"
description: Replace long nested IF formulas in Smartsheet with INDEX MATCH and a reference table. Real examples with months, currency rates and INDEX COLLECT.
image: https://www.adrienautomation.com/hubfs/Nested%20IF%20vs%20Lookup%20Table.png
---

[Skip to content](https://www.adrienautomation.com/blog/smartsheet-nested-if-alternative-index-match-lookup-tables#main-content)

![adrien-leduc-independent-smartsheet-consultant](https://www.adrienautomation.com/hs-fs/hubfs/Logo%20adrien%20automation.png?width=59&height=42&name=Logo%20adrien%20automation.png)Homepage

- [Home](https://www.adrienautomation.com?hsLang=en)
- [Blog](https://www.adrienautomation.com/blog)
- [Services](https://www.adrienautomation.com/services?hsLang=en)
- [Contact](https://www.adrienautomation.com/contact?hsLang=en)
- Case Studies
  
    - [Project Assignment & Resource Management](https://www.adrienautomation.com/blog/how-we-streamlined-project-assignment-and-resource-allocation?hsLang=en)
    - [Project profitability & utilisation reporting](https://www.adrienautomation.com/blog/smartsheet-profitability-utilisation-reporting-case-study?hsLang=en)
    - [Project Portfolio Automation](https://www.adrienautomation.com/blog/stop-chasing-projects-how-we-automated-the-entire-project-portfolio-with-smartsheet-control-center?hsLang=en)
- [Book a call](https://www.adrienautomation.com/book-a-1-hour-call-with-adrien-leduc-independent-smartsheet-consultant?hsLang=en)

- [Home](https://www.adrienautomation.com?hsLang=en)
- [Blog](https://www.adrienautomation.com/blog)
- [Services](https://www.adrienautomation.com/services?hsLang=en)
- [Contact](https://www.adrienautomation.com/contact?hsLang=en)
- Case Studies
  
    - [Project Assignment & Resource Management](https://www.adrienautomation.com/blog/how-we-streamlined-project-assignment-and-resource-allocation?hsLang=en)
    - [Project profitability & utilisation reporting](https://www.adrienautomation.com/blog/smartsheet-profitability-utilisation-reporting-case-study?hsLang=en)
    - [Project Portfolio Automation](https://www.adrienautomation.com/blog/stop-chasing-projects-how-we-automated-the-entire-project-portfolio-with-smartsheet-control-center?hsLang=en)
- [Book a call](https://www.adrienautomation.com/book-a-1-hour-call-with-adrien-leduc-independent-smartsheet-consultant?hsLang=en)

![](https://www.adrienautomation.com/hs-fs/hubfs/Nested%20IF%20vs%20Lookup%20Table.png?width=1774&height=887&name=Nested%20IF%20vs%20Lookup%20Table.png)

# Smartsheet Nested IF Alternative: INDEX MATCH Lookup Tables

![Adrien Leduc](https://www.adrienautomation.com/hs-fs/hubfs/Photo%20profil.jpg?width=48&height=48&name=Photo%20profil.jpg)

 Adrien Leduc

November 11, 2026

Nested IF is fine for two or three conditions. Past that, an INDEX/MATCH against a reference table is shorter, easier to read and changed in one place instead of in every formula.

## The nested IF version

When you build in Smartsheet, nested IF is the first tool most people reach for. It works, and for two or three outcomes it is perfectly fine. The trouble starts when the list of outcomes grows.

Take a simple need: turning a date into the full month name. Here is the nested IF version:

`=IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May", IF(MONTH([Start Date]@row) = 6, "June", IF(MONTH([Start Date]@row) = 7, "July", IF(MONTH([Start Date]@row) = 8, "August", IF(MONTH([Start Date]@row) = 9, "September", IF(MONTH([Start Date]@row) = 10, "October", IF(MONTH([Start Date]@row) = 11, "November", IF(MONTH([Start Date]@row) = 12, "December", ""))))))))))))`

 

That is twelve conditions and about 600 characters for one output format. A few things go wrong from here:

- You need the 3-letter name too? Copy the whole formula into another column and re-type twelve labels.
- You need 01 January for a report? That is a third copy.
- Count the closing parentheses. Miss one and you get an error, and finding which one is tedious.
- The same formula ends up pasted into several sheets. A typo in one of them is hard to spot.

## The reference table version

Now the same need with a [months reference table](https://www.adrienautomation.com/blog/smartsheet-reference-tables-build-once-and-use-them-forever?hsLang=en). The table is a small sheet with one row per month. Here is a slice of it:

| Month number | With leading zero | Full name | Short name | Months Ordered |
| --- | --- | --- | --- | --- |
| 1 | 01 | January | Jan | 01. Jan |
| 2 | 02 | February | Feb | 02. Feb |
| 12 | 12 | December | Dec | 03. Mar |

 

The formula that pulls the full name becomes one line:

`=INDEX({Months Ref Full Name}, MATCH(MONTH([Start Date]@row), {Months Ref Month Number}, 0))`

 

MATCH finds which row of the table holds the month number. INDEX returns the value from that row in the column you ask for. That is the whole mechanism, and it is the same one Smartsheet [documents for cross-sheet lookups](https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations).

Need the short name or the leading zero version? You do not write a new formula. You change the first range to point at a different column:

`=INDEX({Months Ref Short Name}, MATCH(MONTH([Start Date]@row), {Months Ref Month Number}, 0))`

 

The logic stays identical and only the column you return changes. Nothing is re-typed, and there is no closing parenthesis to count.

## Where reference tables really win: values that change

Months never change, so the months example only shows the length problem. Currency conversion shows the flexibility problem, because the list grows and the values move.

Here is a conversion to GBP written with nested IF. The rates below are illustrative:

`=[Amount]@row * IF([Currency]@row = "USD", 0.79, IF([Currency]@row = "EUR", 0.86, IF([Currency]@row = "CAD", 0.58, IF([Currency]@row = "AUD", 0.52, 1))))`

 

Now a client opens an office in Singapore and you need SGD. You open the formula, add a fifth condition, and rebalance the parentheses. Then you do it again in every sheet where that formula was copied. When finance publishes new rates for the year, you repeat the whole exercise with the numbers.

With a reference table, the rates live in one place:

| Currency | Rate to GBP |
| --- | --- |
| USD | 0.79 |
| EUR | 0.86 |
| CAD | 0.58 |
| AUD | 0.52 |

 

And every sheet uses the same formula:

`=[Amount]@row * INDEX({FX Rate}, MATCH([Currency]@row, {FX Currency}, 0))`

 

Adding SGD means adding one row to the table. No formula is edited and no sheet is reopened. When the rates change, you edit the cells in the table and every sheet that references it picks up the new value.

### Going further: one rate per currency and fiscal year

Most finance teams want rates fixed per fiscal year. Nested IF turns into a grid of conditions here, with one branch per currency and year.

A second criterion is where INDEX/MATCH reaches its limit. MATCH searches for one value only, and it cannot combine several criteria at once. The answer is INDEX/COLLECT. COLLECT filters the table on as many criteria as you give it, and the 1 in INDEX(..., 1) returns the first result:

`=[Amount]@row * INDEX(COLLECT({FX Rate}, {FX Currency}, [Currency]@row, {FX Fiscal Year}, [Fiscal Year]@row), 1)`

 

This follows the [multiple criteria pattern in Smartsheet's own help article](https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations). There is no helper column to maintain, and a third criterion such as a region is just one more pair of arguments. Keep one row per currency and year in the table, because the 1 returns the first match if there are several.

COLLECT works best when each criterion sits in its own column. That means a long table, with one row per currency and year and separate Year and Rate columns, instead of a wide one with a column for 2024, another for 2025 and so on. With the wide layout you need extra logic to pick the right year column. I explain why the long structure scales better in [Long Tables: The Smartsheet Table Structure That Makes Formulas Easier](https://www.adrienautomation.com/blog/long-tables-smartsheet-structure-that-makes-formulas-easier?hsLang=en).

A new year is a new set of rows. Add a current-year row per currency that pulls the latest rate, and the sheets that need current-year figures never need updating at the start of the year.

## When nested IF is still the right call

The simplest way to choose is to ask what the formula is doing. IF tests conditions, while INDEX/MATCH looks up values. A month number turning into a month name, a currency into a rate, an employee into a manager are all lookups. Those belong in a table.

Nested IF is still the better fit when:

- You have two or three outcomes and they are unlikely to change.
- The logic compares several columns at once, for example a status that depends on a start date, an end date and a checkbox. That is a set of conditions, not a lookup.
- The formula lives in one sheet only and nobody else will reuse it.
- You are prototyping and want to see the result before building anything.

A useful warning sign: if you find yourself copying the same IF chain into a second sheet, or editing it because a label or a value changed, it was a lookup all along.

## Side by side

Here is how the two approaches compare across the month and currency examples:

|  | Nested IF | INDEX/MATCH + reference table |
| --- | --- | --- |
| Formula length | About 600 characters for 12 months | One short line, whatever the number of rows |
| Another output format | New formula, 12 labels re-typed | Same formula, point to another column |
| Fix or rename a label | Edit every formula where it was pasted | Edit one cell in the reference table |
| Add a new option | Rewrite the formula and re-balance the parentheses | Add a row |
| Readability | Hard to scan past 3 conditions | Always the same two functions |
| Shared across sheets | Copy and paste, then hope they stay aligned | Every sheet reads the same source |
| Setup effort | Build the nested logic | Build the table and its cross-sheet references once |

 

The last row is the honest trade-off. Nested IF needs nothing in advance, while the reference table needs a few minutes of setup. That setup pays back the first time something changes.

## The takeaway

If the formula answers "which value goes with this input", build a reference table and use INDEX/MATCH. You write the logic once, the data lives in one place, and a change becomes a one-cell edit instead of a hunt through every sheet. Keep nested IF for real conditions.

If you want help working out which reference tables your Smartsheet environment could benefit from, get in touch using the form below.

 

## Keep reading

### [![](https://www.adrienautomation.com/hs-fs/hubfs/Smartsheet%20Workday%20Manager%20Hierarchy%20Integration.png?width=1774&height=887&name=Smartsheet%20Workday%20Manager%20Hierarchy%20Integration.png) Smartsheet’s Native Workday Integration: Automate Your Manager Hierarchy](https://www.adrienautomation.com/blog/smartsheets-native-workday-integration-automate-your-manager-hierarchy?hsLang=en)

### [![](https://www.adrienautomation.com/hs-fs/hubfs/Smartsheet-Portfolios-vs-Control-Center.png?width=2805&height=1407&name=Smartsheet-Portfolios-vs-Control-Center.png) Smartsheet Portfolios vs Control Center: Which One Does Your PMO Actually Need?](https://www.adrienautomation.com/blog/smartsheet-portfolios-vs-control-center-which-one-does-your-pmo-actually-need?hsLang=en)

 © 2026 Adrien Leduc · [LinkedIn](https://www.linkedin.com/in/adrienleduc/)

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Adrien Leduc",
    "url" : "https://www.adrienautomation.com/blog/author/adrien-leduc"
  },
  "dateModified" : "2026-11-11T11:20:37.322Z",
  "datePublished" : "2026-11-11T11:20:37.000Z",
  "headline" : "Smartsheet Nested IF Alternative: INDEX MATCH Lookup Tables",
  "image" : [ "https://www.adrienautomation.com/hubfs/Nested%20IF%20vs%20Lookup%20Table.png" ],
  "mainEntityOfPage" : {
    "@id" : "https://www.adrienautomation.com/blog/smartsheet-nested-if-alternative-index-match-lookup-tables",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://www.adrienautomation.com/hubfs/Logo%20adrien%20automation.png"
    }
  }
}
```