# How to find value below a specific cell in another sheet

I have a sheet containing color names on several rows with associated numbers in the row directly below them.

In a separate sheet, I want to use a formula to return the color values in a column next to the color names

## Sample Data

A B C D
1 red blue green yellow
2 10 10 0 500
3 blue yellow
4 10 500
5 blue green
6 10 0
7 red green
8 10 0

## Sample Results

A B
1 red 10
2 blue 10
3 blue 10
4 red 10
5 blue 10
6 yellow 500
7 green 0
8 green 0
9 green 0
10 yellow 500

Data in Sheet1

List of Colors in Sheet2

• Dario, it would be helpful if you edit your question to clarify why there are duplicate combinations in the original data and in the results sheet there are also duplicate colors however there isn't a one-to-one relationship and it isn't clear what the order of numbers is. Will a specific color always have the same value? Do you want to list the duplicates? Are you trying to get a count? etc. Oct 24, 2023 at 15:48

``````=WRAPROWS(TOCOL(A:D,1,1),2)
``````
1. TOCOL stacks the range into a single column:
• `ignore` is set to `1` to skip blanks
• `scan_by_column` is set to `TRUE` or `1`
2. WRAPROWS converts the column into 2 column rows by setting the `wrap_count` to `2`.

Step 1 - Let's get that array of original data into a single column. Since you didn't share the spreadsheet, I made my own with the data in a tab called "Data". All three formulas go in a separate tab ("Solution" in my spreadsheet)

``````=FILTER({Data!A:A;Data!B:B;Data!C:C}, LEN({Data!A:A;Data!B:B;Data!C:C}))
``````

Step 2 - Pull out the colors into their own column: (This filter will show only the even numbered rows)

``````=FILTER(A1:A, MOD(ROW(A1:A), 2) = 0)
``````

Step 3 - Pull out the values under the colors in the adjacent column: (This filter will show only the odd numbered rows)

``````=FILTER(A1:A, MOD(ROW(A1:A), 2) = 1)
``````

You may need to swap the even and odd filters depending on if you have a header row. You could make the first step simpler, but I don't know if you happen to have a ragged array. This formula will work even if you do. Here's my spreadsheet: https://docs.google.com/spreadsheets/d/1Ocbmzv9WCJYpE30bjL7ZQ32C5aj4ve-UD32XW6tGZCs/edit?usp=sharing

To get the lowest number under each unique text label above such number, use `query()` and `filter()`, like this:

``````=lambda(
data,
lambda(
colors, numbers,
query(
{ colors, numbers },
"select Col1, min(Col2)
group by Col1
label min(Col2) '' ", 0
)
)(
filter(data, istext(data)),
filter(data, isnumber(data))
)
)(
{
filter(
flatten(transpose(A1:D100)),
len(flatten(transpose(A1:D100)))
)
}
)
``````

If you are looking for a way to find numbers by their cell fill color, that cannot be done with a formula that uses Google Sheets built-in functions, because none of those functions has access to cell formatting such as fill colors. If that is what you need, you will have to use a script, or devise another way to categorize your data that does not depend on fill colors.