Excel formula that returns row if value is found in a column

Question

I have a list of DNS entries that I need to sort to get the good records.

In sheet1, I have a dump of the raw data, in column 1 is a zone ID which is a number.

In sheet2, I have a column made up of the zone ID's that I want to keep.

On sheet3 I am looking for a way to take sheet1 column 1, to see if it matches one of the values in sheet2 column 1. If it does, then the result should be the entire row into sheet 3.

Is this possible? Data example is below:

Sheet1 - 4 columns

1   foo            A     IP_Address

1   foomaster    CNAME   IP_Address

392 jimmy          A     IP_Address

Sheet2

In column1 is a list of acceptable zone ID's I want.

Sheet3

If value from sheet1-column1 exists in sheet2-column1, paste the entire row from sheet1.

Answer 1

One quick and dirty way to do it is with =COUNTIF(). If the value is found, return the value from cell A1, B1, C1, etc. by filling the formula to the right.

In Sheet 3, Cell A1, enter the following:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)

Now use the Fill right (Ctrl+R) and Fill down (Ctrl+D) features to apply the formula to as many cells as required, depending on the number of columns+rows expected in the raw data you have in Sheet 1. If the search is successful, it will fill out the data from that row in Sheet 1.

If the search is unsuccessful, the row will return FALSE. If a cell on sheet 1 does not have data, it will return 0. If desired, you can return blank text ("") instead of a FALSE or a 0 with a formula like:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")

To say it again - this is quick and dirty and will have performance implications if you have a large dataset. You are typically better off putting your raw data in a database - you can then use a Pivot Table or simple SQL queries to extract the data you need in the format required.

Answer 2

Ok, but you need a third sheet for this trick. In this third sheet, you'll need to drag this formula right and down, to match the size of the table(sheet1) where the values you will be looking for reside. Sheet2 will be where we will try to find said values.

=index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))

So this is like a dragable vlookup where you will be filling a new table the same size as sheet1, in the case it doesn´t find any match it will return #N/A, if you want to handle that then sorround it in a IFERROR like this

=IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")

Then you will need to filter and delete the "NO MATCH" entries. But this is the formula I personally use for this kind of things.

Maybe the ,, ', etc; sintax is not the same to you because of locale and different Excel versions.

Cheers