excel formula - Search for Value in Multiple Columns - Stack Overflow

admin管理员组

文章数量:1401623

Apologies for the image and not uploading a proper table -- still haven't figured out how to do that.

I have a sample data set of agents with multiple columns with random values.

I am trying to use either index-match or Filter functions to allow me to search for the owner of a specific value.

Because the value being search could be in any column, I can't call index-match and the Filter function has the same issue.

Any help would be appreciated.

Apologies for the image and not uploading a proper table -- still haven't figured out how to do that.

I have a sample data set of agents with multiple columns with random values.

I am trying to use either index-match or Filter functions to allow me to search for the owner of a specific value.

Because the value being search could be in any column, I can't call index-match and the Filter function has the same issue.

Any help would be appreciated.

• excel-formula
• index-match

Share Improve this question Follow asked Mar 23 at 22:25 haby22haby22 851010 bronze badges 2

• 1 Confusing. Your filter function uses a different cell for argument compared to the match function. Try =TOCOL(Ifs(B2:F13=K4,A2:A13),2) – P.b Commented Mar 23 at 22:38
• Please do not upload images of code/data/errors. When you edit your post to remove the image, click the ? button on the toolbar, and then click the Tables item. It gives you clear information on how to create text tables here. – Ken White Commented Mar 23 at 23:11

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I don't know if that is possible with a single formula, but I managed to do what you want with multiple small formulas. Let's say this is your Excel table:

	A	B	C	D	E	F
1	Name	Column1	Column2	Column3	Column4	Column5
2	Agent1	883	1849	868	778	883
3	Agent2	1758	1872	722	859	1556
4	Agent3	1208	1418	1707	577	875
5	Agent4	1277	959	669	503	1895
6	Agent5	1541	635	526	1585	592
7	Agent6	1857	1673	1778	1244	777
8	Agent7	1177	1843	987	1213	808
9	Agent8	1257	1474	526	1537	1624
10	Agent9	1487	1348	1786	1172	723
11	Agent10	715	1404	1437	1178	1783
12	Agent11	1697	1831	908	1019	1487
13	Agent12	1273	573	1313	1398	1625

Here is what we will do:

1. Find the match in each column:

First we use the formula =IFNA(MATCH($H$3;$B$2:$B$13;0);0) This will search for the index in the range that matches the value, if no value is present, then the index will be 0. For column B this will be the results

1541 -> 5

715 -> 10

999 -> 0

We will use this match function for each column

2. Create the references for the Column "A":

We will use the results of the previous formula to create the reference for column A with the formula =IF(J5<>0;"A" & J5 + 1;"") This will check if the index is different than cero and then format the cell as a concatenation of A + the index plus one:

1541 -> 5 -> A6

715 -> 10 -> A11

999 -> 0 -> ""

3. Find the first not empty reference:

The previous step will give us a range of references if multiple value matches, for this, we will use this formula to get the first non blank value:

=INDEX($K$5:$K$9;MATCH(FALSE;EXACT("";$K$5:$K$9);0))

The formula will lookup the range of references and return the first non empty value:

Range	Result
""	A2
""
A2
""
""

4. Fetch the value with the reference

Once we have all the previous values we can fetch the actual value from column A with the recerence using the formula =INDIRECT($K$5;TRUE)

The function "INDIRECT" will receive the value "A2" and use if as a reference for the cell A2 giving us the value "Agent2" in the original table.

Result Table

Here we have all the formulas used to find the match:

	H	I	J	K	L
1
2	Value	Agent
3	635	=INDIRECT($K$5;TRUE)
4
5	Column1	=IFNA(MATCH($H$3;$B$2:$B$13;0);0)	=IF(J5<>0;"A" & J5 + 1;"")	=INDEX($K$5:$K$9;MATCH(FALSE;EXACT("";$K$5:$K$9);0))
6	Column2	=IFNA(MATCH($H$3;$C$2:$C$13;0);0)	=IF(J6<>0;"A" & J6 + 1;"")
7	Column3	=IFNA(MATCH($H$3;$D$2:$D$13;0);0)	=IF(J7<>0;"A" & J7 + 1;"")
8	Column4	=IFNA(MATCH($H$3;$E$2:$E$13;0);0)	=IF(J8<>0;"A" & J8 + 1;"")
9	Column5	=IFNA(MATCH($H$3;$F$2:$F$13;0);0)	=IF(J9<>0;"A" & J9 + 1;"")
10

本文标签： excel formulaSearch for Value in Multiple ColumnsStack Overflow