Do you want to get a value from a specific cell in Excel when using Power Automate?

Power Automate has a connector with Excel, but unfortunately, the available actions are limited and mostly work with table objects.

this means getting a value from a single cell outside of a table object can be tricky.

This post is going to show you all the ways you can get a cell value in your flow.

Get Cell Value from a Table

The first method will allow you to get a cell value from a table.

This will act very similarly to a VLOOKUP function and allow you to return a cell value from the table based on a lookup value.

Just like VLOOKUP, if there are multiple matches you can return the first match.

For example, suppose you want to get the first instance of the Subaru Make and return the corresponding Legacy Model from the above table.

You can use the List rows present in a table action from the Excel connector to do this.

1. Add the List rows present in a table action to your flow.
2. Select the SharePoint site Location of your Excel file.
3. Select the Document Library that contains your Excel file.
4. Select the Excel File in your document library.
5. Select the Table in your Excel file.
6. Click on the link to Show advanced options in the action.

The Advanced options will allow you to use a Filter Query so you can return only the results from the table that match the given criteria.

This can potentially return multiple records from your table, but you can then use an expression to get the first value from your array.

7. Add your Filter Query such as Make eq 'Subaru' to the action. This example will only return the rows from the table where the Make field contains the value Subaru.

You can then get the results with the value dynamic content block.

[
  {
    "@odata.etag": "",
    "ItemInternalId": "66d6f949-4a88-428b-9f22-60952ee8f531",
    "Make": "Subaru",
    "Model": "Legacy",
    "Year": "2009"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "3386c981-ddb6-436f-b3eb-f7faca9b10e0",
    "Make": "Subaru",
    "Model": "Outback",
    "Year": "2005"
  }
]

When the flow runs, it will return the above two records from the Excel table.

@{outputs('List_rows_present_in_a_table')?['body/value']?[0]?['Model']}

You can skip the value dynamic content and get a single value from the output with a simple expression. The above expression will get the Model value from the first record of the value content.

The [0] part of the expression will return the first record. This uses a 0-based index, so the first record has a 0 index, the second has a 1 index, etc.

The ['Model'] part of the expression will return the value from the Model field key-value pair.

The result for this example will return the value Legacy.

Get Cell Value from a Table with a Unique Identifier

There is a slightly more direct way to get a single value from a table if your table has a column with a unique ID.

This will work only if your table has a column of unique values. For example, the above table now has an ID column that uniquely identifies each row.

The Get a row action allows you to specify a value from a key column and it will only return that row.

1. Add the Get a row action to your flow.
2. Select the SharePoint site Location of your Excel file.
3. Select the Document Library that contains your Excel file.
4. Select the Excel File in your document library.
5. Select the Table in your Excel file.
6. Select the Key Column that uniquely identifies each row.
7. Enter a Key Value from the row in your table that you want to return. This could be dynamic content from another action.

Now you will be able to access each field in the record that is returned from the Get a row action. These dynamic content blocks will return a single value since the Get a row action can only return one row.

Get Cell Value from a Cell

You have just seen how to get a single value from a table when the table does or does not contain a key column.

What if the value you want to get from your flow is not inside a table, but instead is in a regular cell?

Unfortunately, all the actions that allow you to get data from Excel only work with table objects.

Thankfully there is a very versatile action that can be used for this purpose. The Run script action allows you to run an Office Script in your Excel workbook and then return values from the script to your flow.

This means you can create a script to get a value from a cell outside of a table based on the cell address.

You will first need to create the script in Excel.

Go to the Automate tab and select the New Script option.

function main(
    workbook: ExcelScript.Workbook,
    sheetName: string,
    cellAddress: string
) {
    let sheet = workbook.getWorksheet(sheetName);
    let cellValue = sheet.getRange(cellAddress).getValue();
    return cellValue;
}

Paste the above Office Script into the Code Editor and press the Save script button. Your script is now ready to run from Power Automate.

📝 Note: This is a generic script and can be run on any Excel file that is selected in Power Automate.

The script takes two input parameters for the sheet name that you want to get the cell value from sheetName and the cell address for the value you want sheetName.

These parameters will be passed to the script from the Power Automate Run script action.

The script then uses the getValue() method to get the value from the cell and then the return command is used to pass this value back to Power Automate.

📝 Note: The getValue() method will only return the value from a single cell. You will need to use the getValues() method to return values from a range with multiple cells.

In the above example, cell C4 contains a value resulting from an XLOOKUP formula. You can now use the Run script action to get this value even though it’s outside of the table.

Now you will be able to use the Run script action to get the cell value.

1. Add the Get a row action to your flow.
2. Select the SharePoint site Location of your Excel file.
3. Select the Document Library that contains your Excel file.
4. Select the Excel File in your document library that you want to get the value from.
5. Select the Get Cell Value script you created in Excel.
6. Fill in the sheetName parameter with the name of the sheet that contains the cell value you want to get. In this example the cell is on a sheet named Data.
7. Fill in the cellAddress parameter with the cell address. This is the column letter and row number of the cell. In this example the cell is C4.

When this action runs, it will produce the above output with the value in the body.

The value can easily be accessed using the result dynamic content.

Get Cell Value from a Named Range

The script to get a single cell value can be adjusted slightly to return all the values in a range.

You can even use a named range instead of the generic range address. In this example, the range B2:C5 has been named myRange.

function main(
    workbook: ExcelScript.Workbook,
    sheetName: string,
    cellAddress: string
) {
    let sheet = workbook.getWorksheet(sheetName);
    let cellValue = sheet.getRange(cellAddress).getValues();
    return cellValue;
}

The script needs to be adjusted slightly to use the getValues() method shown above. This will allow the script the return an array of values from the range back to Power Automate.

Now you can reference the named range in the Run script action and it will return the values in the range. This can be a more dynamic option since the range can be moved in the Excel file without needing to update the address used in Power Automate.

[
  [
    "ID",
    1
  ],
  [
    "Make",
    "Honda"
  ],
  [
    "Model",
    "Accord"
  ],
  [
    "Year",
    2012
  ]
]

The result dynamic content will return an array of arrays in this example, so if you want a particular value from the range you need to create an expression to access that value.

Get Cell Value from a Dynamic Array

You can also get all the values from a dynamic array formula. This will use the same getValues() method in the Office Script.

The above example shows an XLOOKUP formula that returns multiple values. This can be referenced in the Run script action with the # character at the end of the top leftmost cell address of the array.

In this example the array formula is in cell E3, so E3# will reference the entire array of values returned from the dynamic array formula.

Conclusions

The Excel connector in Power Automate only has dedicated actions to get values from Excel tables.

If the value you want from Excel is outside a table, there is no obvious way to get this in your flow.

Thankfully, the Run script action you can pass cell values, ranges of values, and even dynamic arrays back to your flow.

Did you ever need a single cell value from Excel in your flows? How did you get this? Let me know in the comments!