Google Sheets: Lookup Row, Lookup Column then Update Cell

This is an automation I’ve been struggling with for 5+ years, wonder if anyone has any insight.

Here’s the flow (with an example):

My users fill a gravity form denoting their favourite dessert, which then gets updated in a Google Sheet.

I’ve been trying for years to get either a Zapier zap or a Make scenario to do the following:

  1. Use “Search Rows” to find the person, for example “Mother In Law”
  2. Then search the column headers to find “Dog Treat”
  3. Then update the corresponding cell, as marked in the screenshot

Of course, finding the “person” in column A is no problem at all, but I am stymied with searching the column. I attempted to create a data store in the sheet that says “Dog Treat = Column F” but I still can’t update it.

I feel like I might be missing something really small and stupid, anyone have any insight?

Hi @Mike1, I’m sharing a way to do this automation.

Process executed

I create 2 variables with the input data, these would come in your gravity form

Using the Switch module I map the value of the food variable with the column in the spreadsheet

Then I look for the row where the person is located in column A

I updated the spreadsheet using the column obtained from the Switch module and the row number from the search action.


What I’ve been wrestling with for years was solved by you in 45m.
This works exactly as expected, thank you very much for your efforts and explanations.