CODEWITHSUNDEEP
google-sheets
Davide Marchesi
Davide Marchesi

Reputation: 7

Searching value in a matrix Google Sheet

I have a Google sheet in which I have a table like this:

+--------+------------+---+---+---+
| Name A | Category A | 3 | 0 | 0 |
+--------+------------+---+---+---+
| Name B | Category B | 1 | 4 | 5 |
| Name C | Category C | 2 | 6 | 0 |
+--------+------------+---+---+---+

In another sheet of the same file I need a function to find each number and insert in the cell the Name of that record line.

For example for number 4 I will receive Name B.
The problem is that I can't find a function that is able to find in a matrix.
The 0 I put here are void cells and I don't need to find them.
I'm sure that every number isn't repeated.

Upvotes: 0

Views: 886

Answers (1)

Taeyoung Ahn
Taeyoung Ahn

Reputation: 51

enter image description here

Maybe this is what you're looking for?

=IFERROR(VLOOKUP(H2,{D:D,B:B},2,0),IFERROR(VLOOKUP(H2,{E:E,B:B},2,0),VLOOKUP(H2,{F:F,B:B},2,0)))

Or you could also use this formula:

=UNIQUE({B:C,D:D;B:C,E:E;B:C,F:F})

to convert matrix into long data format and simply use vlookup.

enter image description here

Upvotes: 1

Related Questions