What formula can I use to find the text of a cell, within a table?

[mpatino]

Hi experts,

Just wondering how can I do to find a value inserted into a specific cell within a table and return the row number. For example, I have a table range A1:T2000 and I want to find within this table without specifying a column a value that I type in U2, so what I need is to check all the cells within that table range and return the row number where the value is found.

Thank you,

 

[BlueHornet]

This is a kind of question that, without knowing your data as well as you do, and how discrete it is - that is, whether there may be only one or two instances of the datum in question, or hundreds - I would hesitate to answer with "a formula". In some of the tables that I routinely encounter within Excel, and also depending on how specific the question (whether you're looking for the occurrence of a word, for example, or a very esoteric part number that may very well be unique in the table) I couldn't possibly make a single formula that would return meaningful results.

But filters are another matter.

I'd suggest that you look into Advanced Filters, where you can, for example, make a wildcard query for "*" & MyString & "*" across every column in the table being queried (you'd want to make the entry on a separate line for each column, unless you expect the data to be repeated across every column of a table where it occurs). You can enter MyString as you propose at U2, and then simply refer in your Criteria rows to: = "*" & $U$2 & "*", again, using that formula in Criteria Row 1 for the first column, in Criteria Row 2 for the second column, etc. and on to Criteria Row "n" for the nth column. When you run the query you'll get a subset of the table that meets the query criteria that you've specified.

If the table rows are numbered, then those numbers can be brought into the query results.

 

[inactiveuserps07]

@mpatino, this is normally the type of thing you'd set up a helper column for. However, since you seem to only need one result, we can skip the helper column by making the formula longer. If your text to search is entered into U2, enter the following formula into V2 to return the row number where the text in U2 is found within A2:T2000 ...

=SUMPRODUCT((ISNUMBER(FIND($U$2,A2:A2000&"/"&B2:B2000&"/"&C2:C2000&"/"&D2:D2000&"/"&E2:E2000&"/"&F2:F2000&"/"&G2:G2000&"/"&H2:H2000&"/"&I2:I2000&"/"&J2:J2000&"/"&K2:K2000&"/"&L2:L2000&"/"&M2:M2000&"/"&N2:N2000&"/"&O2:O2000&"/"&P2:P2000&"/"&Q2:Q2000&"/"&R2:R2000&"/"&S2:S2000&"/"&T2:T2000)))*ROW(A2:A2000))

If the text in U2 is not found, you will get a row # of 0.

If you want to know the entire cell reference (i.e., column and row) where the text in U2 is located, you could use this megaformula is V2:

=IFERROR(IF(U2="","",CHAR(64+MATCH("*"&$U$2&"*",OFFSET($A$1,SUMPRODUCT((ISNUMBER(FIND($U$2,A2:A2000&"/"&B2:B2000&"/"&C2:C2000&"/"&D2:D2000&"/"&E2:E2000&"/"&F2:F2000&"/"&G2:G2000&"/"&H2:H2000&"/"&I2:I2000&"/"&J2:J2000&"/"&K2:K2000&"/"&L2:L2000&"/"&M2:M2000&"/"&N2:N2000&"/"&O2:O2000&"/"&P2:P2000&"/"&Q2:Q2000&"/"&R2:R2000&"/"&S2:S2000&"/"&T2:T2000)))*ROW(A2:A2000))-1,0,1,20),0))&SUMPRODUCT((ISNUMBER(FIND($U$2,A2:A2000&"/"&B2:B2000&"/"&C2:C2000&"/"&D2:D2000&"/"&E2:E2000&"/"&F2:F2000&"/"&G2:G2000&"/"&H2:H2000&"/"&I2:I2000&"/"&J2:J2000&"/"&K2:K2000&"/"&L2:L2000&"/"&M2:M2000&"/"&N2:N2000&"/"&O2:O2000&"/"&P2:P2000&"/"&Q2:Q2000&"/"&R2:R2000&"/"&S2:S2000&"/"&T2:T2000)))*ROW(A2:A2000))),"NO MATCH")

 

[mpatino]

Hi Erik, thank you very much for the information. I've actually decided to use helper columns and concatenate all the cells in the row, with the helper columns and the concatenation I was able to create a dynamic search. Really appreciate you taking the time to reply to my post.

 

[mpatino]

thanks a lot for your inputs and suggestions BlueHornet.

 

[inactiveuserps07]

@mpatino, using a helper column, you just needed =IF(ISNUMBER(MATCH("*"$U$2&"*",A2:T2,0)),"x","") copied down the helper column, after which V2 could index the helper column for "x" or the data could be sorted in descending order by the helper column to bring all rows marked with an "x" to the top (if there are multiple instances of "x").

Be careful that your concatenated helper column includes delimiters between cell values, or you could wind up in trouble. For instance if adjacent cells contained "idea finish earphones," concatenating them without delimiters would also find "deaf" and "shear." In real life, this could be a problem if you have serial numbers or other similar data in the range, since they'd run together and possible produce false matches.

But if it works for your current needs as-is, all's well that ends well. At least know there are other options should the need arise.