2

I have a massive excel sheet with a few thousand rows and columns. I need to identify those rows that have a certain code. The code can be in any column.

Here is an example table to make this more clear:

# code code code code
1 999  999  313  999
2 999  999  999  999
3 999  313  999  999
4 999  999  999  313
5 999  999  999  999

Which function should I use to identify those with the code 313 and document it a new column, like this:

# code code code code new column
1 999  999  313  999    1
2 999  999  999  999  
3 999  313  999  999    1
4 999  999  999  313    1
5 999  999  999  999

ISNUMBER function works for one column, but didn't manage to expand it to a table (multiple columns).

Many thanks for your help!

2 Answers 2

1

Try this: =IFERROR(SIGN(MATCH(313,B2:E2,0)),"")

If you omit SIGN() you'll also get the relative column number, not only whether it's present or not.

Update 1

If you want to match also cells containing (column H) / starting with (column G) specific numbers, than the formula is a bit more complex, as you need to convert values to text too. The formula now is: =IFERROR(SIGN(MATCH(G$1,INDEX(TEXT($B2:$E2,"0"),0),0)),"")
Where G1 is the pattern you want to match.

enter image description here

0
1

You can use COUNTIF():

enter image description here

In this example the data is in columns A through I

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .