How to return the column head if a cell is matched?
Ooker
Say I have a table:
I want to search for a cell, and return its column header. For example, if I search for Orange, the result is Fruit. I can achieve this by using the nested if (E2 is Orange in here):
=IF(NOT(ISERROR(MATCH(E2,A1:A4,0))),A1,IF(NOT(ISERROR(MATCH(E2,B1:B3,0))),B1,C1))
But in a large table this is impossible. Plus that I cannot know if error happens. Is there a better way to get the result? I don't know how many rows each column has. There is no duplicated data in the table. The table does not start in A1, and can be moved to another spreadsheet in the future.
yass
If Aggregate is not between the functions you can use:
=OFFSET(A1,0,SUMPRODUCT(--(A2:C4=E2)*COLUMN(A2:C4))-1)
Sumproduct will determine the column number for the matching value you are searching for.
Offset will return the Header of the equivalent column.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: What's the default filesystem installed on vmware vCenter?
- Next: How can I save several separate text with AutoHotKey?
Related
Related Related
- 1
How to find matched rows by skipping certain column(s)?
- 2
How to return matched results in ActiveRecord and then remainder?
- 3
How to return pattern-matched element?
- 4
How to read a column cell in Listview
- 5
How to return just the matched elements from a mongoDB array
- 6
How to return matched regex in R gsub
- 7
IF duplicate cell value found in column then return value
- 8
How to return matched value with .NET regex?
- 9
grep - how to return only matched part of string on a column
- 10
How to find by array and return nil if not matched?
- 11
How to return cell address as string inside the cell ?
- 12
How to specify column to add cell to
- 13
if a column contains a specific text, copy column A matched to my cell text using VBA
- 14
How to return 0 if a pattern is matched from a file?
- 15
How to return 0 if a pattern is matched from a file?
- 16
How to return pattern-matched element?
- 17
How to read a column cell in Listview
- 18
How to get cell values based on matched criteria in excel?
- 19
How to return matched regex in R gsub
- 20
Excel - Multiple column search and return value from matched cells
- 21
Check what keyword, from a list, a cell starts with and return matched keyword
- 22
How to extract the value corresponding to same head row and head column of a file?
- 23
Excel to return matched value in rows from a column array
- 24
Return column providing the mean of matched values
- 25
How to return the index of a cell which holds the largest valued element of a given column
- 26
how to match a column in jquery for a text match and return another column from matched rows
- 27
Return a column heading from a table for a specific cell
- 28
How to return column value in another sheet if a cell contains "Pick up"
- 29
Excel: Search for if a cell contains any of the values from a list of possible values and return all that matched (in a cell)
Comments