with copy 2048x1280 x2
17. March 2023

How to use AI with INDEX MATCH, VLOOKUP and IF functions

Ever found yourself struggling with complex syntax and time-consuming searches for the right formulas? Us too. That’s why we developed our AI-powered Formula Assistant, an easy-to-use feature that can help you maximize the potential of popular Google Sheets and Excel functions like INDEX & MATCH, IF, and VLOOKUP. In a few words, you can prompt the AI assistant to suggest a formula to fit your needs without having to spend time searching through documentation or memorizing complex syntax.

Let’s explore how you can unlock the power of the VLOOKUP, INDEX & MATCH, and IF functions beyond your traditional spreadsheets using GRID’s AI Formula Assistant. 

The GRID Formula Assistant: AI at the cellular level

GRID’s AI formula copilot is powered by OpenAI's GPT technology, making it easy for anyone, of any skill level, to quickly find and apply the spreadsheet formulas they need, even if they’re encountering them for the first time. GRID is especially good at helping you combine and calculate data from different data sources, including Notion databases, Airtable bases, Google Sheets, and of course Excel.

Our copilot lives in GRID Sheets, the first spreadsheet editor to offer fully-integrated AI formula suggestions that originate directly within the sheet’s cells. Activate the copilot by writing your prompt directly into the cell where you want the formula to be, and get powerful assistance right where you’re used to working.

slash slash gif

The Formula Assistant’s cell-based nature also means that you’ll never have to switch between tabs or views while working. Your formula-building experience is completely streamlined and surprisingly fast. And we’re having a bit of fun with it too: in a playful subversion of traditional formula syntax, we’ve replaced the equals sign with double slashes: // is the new =

Maximize your VLOOKUP skills with AI

The VLOOKUP function is a powerful tool for quickly finding and retrieving data from large tables or data sets. This function searches for a specific value in the first column of a table and then returns a corresponding value in the same row from a specified column. VLOOKUP can be particularly useful in situations where you need to analyze large amounts of data or create reports from large datasets, and it can also be used to perform data analysis on financial reports or sales data.

In the example below, VLOOKUP can be used to extract “Country” from the database. Where you’d normally use this syntax in your spreadsheet: =VLOOKUP("Brazil",A:B,2,FALSE), you can instead enter a prompt into the cell of your GRID Sheet that looks like this:
// look up Brazil in column A and match with B

Vlookup GIF

Stop struggling with INDEX and MATCH

The INDEX and MATCH functions are useful for retrieving data from large tables or data sets. While the VLOOKUP function is commonly used for this purpose, INDEX and MATCH can be even more versatile in certain situations. The INDEX function returns the value of a cell in a specified location within a table or range, while the MATCH function searches for a specified value within a range and returns its relative position. By combining these functions, you can create complex lookup formulas that can search for values across multiple columns or rows.

Some use cases where INDEX and MATCH formulas would be useful include financial modeling, budgeting, and forecasting, where you may need to analyze data from multiple sources or perform complex calculations on large datasets. They can also be used in GRID to create dynamic dashboards and reports that update automatically based on user input.

The following example involves identifying which sales representative has achieved the highest number of sales. We could use: =INDEX(A:A,MATCH(MAX(B:B),B:B,0)) but that’s a little complex.
We can more intuitively prompt the Formula Assistant with: // match column A with the highest value in column B.

IF Function LP - sumifs GIF

Generate IF formulas faster with AI

The IF function is fundamental for data analysis and modeling. It allows you to evaluate a logical test and return one value if the test is true, and another value if the test is false. This function is particularly useful when you need to perform calculations or make decisions based on specific criteria.

Another use case for the IF function could be in the analysis of student grades and calculating pass/fail rates based on a specific minimum score. It can also be used to identify trends in data or to highlight specific values that meet certain criteria. The IF function can contribute to greater clarity when making decisions based on specific criteria.

Here we’re looking at calculating how many sales a particular representative made in the month of January. Typically you’d use: =SUMIFS(B:B,A:A,"John",C:C,January") but our Formula Assistant lets you explore options more easily using a prompt like: // sum column B if A is John and C is January

Index & match formula assistant

And it doesn’t stop there! Our Formula Assistant can suggest an almost unlimited range of possible formulas for you based on the plain-language prompts you enter. It’s a versatile tool that works with you, so you can save time and get more done. 

Our formula assistant is a means to transcend a common knowledge barrier in data work. It helps users explore an incredible range of real-world scenarios.

Hjalmar Gislason, CEO of GRID

More blog posts

17. May 2024

Unleashing the power of interactive calculators with GRID

Discover how GRID's innovative spreadsheet engine lets you create interactive, dynamic calculators for your website without coding or the need...

05. April 2024

GRID: Your end-to-end lead-generation solution

With seamless embedding and integrations with platforms like HubSpot, GRID has now become an end-to-end lead-generation solution. Send your lead...

15. February 2024

Build a website calculator for lead generation in 4 steps

Transform spreadsheets into interactive, lead-generating calculators with GRID, enhancing lead qualification and website conversion rates.

22. November 2023

Tool building in GRID by guest blogger Vincent Doedee

In this guest post by Vincent Doedee, he describes how he's been able to use GRID to leverage his spreadsheets...

27. September 2023

Monetizing expertise through spreadsheet models

The blog discusses monetizing expertise using spreadsheet models, highlighting how domain experts can benefit from GRID's features to enhance their...

22. August 2023

Create beautiful charts with your Airtable bases in 3 simple steps

Learn how to use GRID to make charts and reports from your Airtable bases.

17. August 2023

6 great ways AI can improve your data work

Improve the quality, accuracy, and accessibility of your datavis with GRID’s AI assistants.

08. August 2023

Emre Toker: Empowering entrepreneurs with GRID

How startup founders and investors use GRID to make the best possible decisions.

26. June 2023

How to create a dashboard from your Airtable base

Learn how to visualize your Airtable bases and create a dashboard.