Ever wanted to find the position of a specific item in each column in Google Sheets?

Let me show you a fun example. I asked three people to rank four food items based on their preferences. Then, I organized their responses in a table like this:

Now let’s say I want to check where “Maryland crab cakes” ranks in each person’s list — in other words, find the rank of an item in each column in Google Sheets.

Since there are only 4 items and 3 users, you can eyeball it:

• Tom → 1
• Clara → 4
• Michael → 4

But what if the list was longer? Manually checking every item wouldn’t be practical. So, let’s automate it with a formula.

Find the Rank of an Item in Each Column in Google Sheets

Here’s how to do it with a formula.

Let’s assume:

• You type the item you’re checking (like “Maryland crab cakes”) into cell F2
• Your ranking table is in columns A to D — with ranks in A and names in B to D

Paste this formula in G2:

=LET(
  unpivot, SORT(SPLIT(FLATTEN(A2:A & "|" & B1:D1 & "|" & B2:D), "|")),
  unpivotc, FILTER(unpivot, CHOOSECOLS(unpivot, 3) <> ""),
  rank, CHOOSECOLS(unpivotc, 1),
  name, CHOOSECOLS(unpivotc, 2),
  item, CHOOSECOLS(unpivotc, 3),
  TOROW(FILTER(rank & "] " & name, item = F2))
)

What the Formula Returns (Example Output)

1] Tom
4] Clara
4] Michael

So, Tom gave it the top rank, while Clara and Michael placed it fourth.

Change the item in F2 to “Tater Tot”, and the formula will give you:

2] Clara
3] Michael
4] Tom

It’s that simple.

What the Formula’s Doing Behind the Scenes

Let me quickly walk you through what’s happening behind the scenes.

We’re first transforming (or unpivoting) the table from a wide format to a tall one, where each row represents a single ranking. We’re also sorting it by rank (the first column). Here’s the part of the formula that does that:

SORT(SPLIT(FLATTEN(A2:A & "|" & B1:D1 & "|" & B2:D), "|"))

Since the ranges are wide open, this can sometimes result in a few trailing or blank values — especially in the first column. So, we need to clean that up. This part of the formula handles that:

FILTER(unpivot, CHOOSECOLS(unpivot, 3) <> "")

That gives us a clean table like this:

Related: Unpivot Data in Google Sheets (Reverse Pivot Formula Explained)

Next, we filter this table to return only the rows where the item matches what you’ve typed in F2, and pair each matching rank with the corresponding name.

This line does the trick:

FILTER(rank & "] " & name, item = F2)

And finally, TOROW lays out the result horizontally across a single row so it’s easy to read in the sheet.

Check the Rank of All Items in One Go

If you have multiple items to check (say, in cells F2:F5), you can use this version to apply the formula to each one:

=LET(
  unpivot, SORT(SPLIT(FLATTEN(A2:A & "|" & B1:D1 & "|" & B2:D), "|")),
  unpivotc, FILTER(unpivot, CHOOSECOLS(unpivot, 3) <> ""),
  rank, CHOOSECOLS(unpivotc, 1),
  name, CHOOSECOLS(unpivotc, 2),
  item, CHOOSECOLS(unpivotc, 3),
  MAP(F2:F5, LAMBDA(r, TOROW(FILTER(rank & "] " & name, item = r))))
)

This way, you’ll get the rankings of each item across all the columns in one shot — super helpful when dealing with larger data.

Wrapping Up

And that’s how you can find the rank of an item in each column in Google Sheets — whether it’s just one item or a whole list.

This approach is great for survey responses, customer feedback, peer reviews — basically anything where you’ve got rankings spread across columns and want to pull them together by item.

Related Google Sheets Tutorials

• Compare Two Strings Irrespective of Word Positions in Google Sheets
• Calculate Percentage Position Within a Range in Google Sheets
• Compare and Highlight Ranking Changes in Google Sheets
• How to Rank Data Alphabetically in Google Sheets
• Break RANK Ties Alphabetically in Google Sheets