Formula to return entire row if certain cell values are true/exact

[K-Dog]

Hello, Forum. On the info below (also attached) I'd like to search for 2 of the countries that exist in C and return only those rows in a new tab. I played with VLOOKUP but I'm not savvy enough to remove the N/As or get the entire row instead of one column. Any help is appreciated!

Name ID Country
Smith 12345 US
Jones 54321 CA
Johnson 34512 DE
Harris 21543 UK
Knight 54312 US

Thanks!

[alansidman]

Here is a VBA solution/Run it once for each country you select

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[FlameRetired]

Another way.

Apply the second range in a new tab.

With target countries in E1:F1 array enter this formula in E2, fill down and across until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1	Name	ID	Country		DE	US
2	Smith	12345	US		Smith	12345	US
3	Jones	54321	CA		Johnson	34512	DE
4	Johnson	34512	DE		Knight	54312	US
5	Harris	21543	UK
6	Knight	54312	US