SOLVED
Home

Excel search

%3CLINGO-SUB%20id%3D%22lingo-sub-178268%22%20slang%3D%22en-US%22%3EExcel%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178268%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20excel%20spreadsheet%20has%20~50000%20rows%20of%20names%20and%20~25%20columns%20of%20relevant%20information.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20like%20to%26nbsp%3Bsearch%2C%20find%20and%20highlight%20~2000%20(of%20the%2050000)%20names%20and%20relevant%20column%20information.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20easiest%20way%20to%20do%20it%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAny%20help%20in%20this%20regard%20is%20highly%20appreciated.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EV%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-178268%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178317%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178317%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Vivek%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20note%20that%20you%20have%20to%20use%20two%20techniques%2C%20one%20for%20highlighting%2C%20and%20one%20for%20lookup.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%20has%20explained%20the%20first%20one.%3C%2FP%3E%0A%3CP%3EBut%20you%20don't%20have%20to%20create%20a%20helper%20column%2C%20you%20can%26nbsp%3Bdirectly%20put%20that%20formula%20into%20the%20Conditional%20Formatting%20rule.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20regards%20to%20the%20second%20one%2C%20you%20can%20depend%20on%20VLOOKUP%20function%2C%20with%20the%20support%20of%20COLUMNS%20function%20as%20follows%3A%3C%2FP%3E%0A%3CPRE%3E%3DVLOOKUP(%24E2%2C%24A%242%3A%24C%241000%2CCOLUMNS(%24A%241%3AB%241)%2C0)%3C%2FPRE%3E%0A%3CP%3E%3CSTRONG%3EWhere%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%24E2%20is%20the%20lookup%20value%3B%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%24A%242%3A%24C%241000%20is%20the%20lookup%20range%3B%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECOLUMNS(%24A%241%3AB%241)%20is%20column%20index%20number%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E0%20represents%20FALSE%20which%20is%20turn%20on%20the%20exact%20match.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENOTE%3A%3C%2FSTRONG%3E%20I've%20used%26nbsp%3B%3CSPAN%3ECOLUMNS%20function%20in%20column%20index%20number%20argument%20to%20make%20the%26nbsp%3Bcolumn%20index%20number%20expandable%20as%20you%20drag%20the%20formula%20to%20the%20right%20so%20that%20you%20don't%20have%20to%20change%20it%20manually%20for%20each%20column.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EI've%20updated%20the%20workbook%20with%20the%20solution%2C%20please%20find%20it%20in%20the%20attachments.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EHope%20that%20helps%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3EHaytham%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178316%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178316%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3EIngeborg%20Hawighorst%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWorked%20really%20well!!!%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20saving%20my%20weekend%2C%20and%20a%20lot%20of%20time!!!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBest%20regards%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EViv%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178292%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178292%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3Ein%20the%20following%20screenshot%2C%20I%20moved%20the%20list%20to%20column%20F%20and%20created%20a%20helper%20column%20in%20column%20D%20with%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(COUNTIF(F%3AF%2CA2)%26gt%3B0%2CTRUE%2CFALSE)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ein%20cell%20D2%20and%20copied%20down.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20I%20selected%20the%20whole%20table%20from%20row%202%20to%20the%20bottom%20and%20created%20a%20new%20conditional%20formatting%20rule%20with%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%24D2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20selected%20a%20yellow%20fill%20and%20confirmed%20all%20dialogs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F31344iD1232F6219F307AD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222018-04-02_09-01-55.png%22%20title%3D%222018-04-02_09-01-55.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178289%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178289%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haytham%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20your%20response!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20see%20the%20attached%20excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20abridged%20my%20table%20here%20with%20~1000%20rows%20and%203%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20need%20to%20find%20the%2020%20IDs%20highlighted%20in%20column%204%26nbsp%3Bfrom%20column1.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20to%20use%20the%20index%2Fmatch%2Fvlookup%26nbsp%3Bin%20this%20case%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178288%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178288%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20have%20the%20list%20of%202000%20names%20somewhere%20in%20the%20workbook%2C%20you%20can%20add%20a%20helper%20column%20to%20the%20original%20data%20table%20with%20a%20formula%20along%20the%20lines%20of%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Dif(countif(Namelist!%24A%3A%24A%2C%5B%40Name%5D)%26gt%3B0%2CTrue%2Cfalse)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20look%20at%20the%20name%20in%20the%20current%20table%20and%20find%20it%20in%20the%20list%20on%20the%20other%20sheet.%20Return%20TRUE%20if%20found%20and%20FALSE%20if%20not.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20you%20can%20use%20conditional%20formatting%20on%20your%20table%2C%20using%20a%20formula%20that%20looks%20at%20the%20value%20in%20the%20helper%20column.%20Select%20the%20table%20and%2C%20assuming%20the%20current%20cell%20is%20in%20row%202%20the%20Helper%20column%20is%20in%20column%20Z%2C%20use%20a%20formula%20like%20this%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%24Z2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20value%20of%20Z2%20is%20either%20TRUE%20of%20FALSE%2C%20so%20you%20don't%20need%20a%20complex%20formula.%20Just%20make%20sure%20that%20the%20column%20reference%20has%20a%20%24%20sign%20and%20the%20row%20number%20has%20NO%20%24%20sign%20in%20front%20of%20it.%20Then%20select%20a%20fill%20color%20to%20highlight%20and%20apply%20the%20rule%20to%20your%20whole%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20this%20approach%2C%20you%20can%20add%20more%20names%20to%20your%20list%20of%202000%20and%20they%20will%20automatically%20be%20highlighted%20in%20the%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20if%20that%20works%20for%20you.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-178275%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-178275%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Vivek%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%202000%20names%20are%20separate%20somewhere%2C%20and%20these%20names%20are%20unique%20identifiers%2C%20you%20can%20depend%20on%20them%20to%20return%20each%20name's%20info%20from%20the%20master%20dataset!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20by%20using%20some%20lookup%20functions%20such%20as%20VLOOKUP%2C%20INDEX%2FMATCH.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20so%2C%20please%20attach%20a%20sample%20of%20them%20to%20show%20you%20how%20to%20do%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

My excel spreadsheet has ~50000 rows of names and ~25 columns of relevant information.

 

I would like to search, find and highlight ~2000 (of the 50000) names and relevant column information.

 

What is the easiest way to do it?

 

Any help in this regard is highly appreciated.

 

Thanks,

 

V

6 Replies
Highlighted

Hi Vivek,

 

If the 2000 names are separate somewhere, and these names are unique identifiers, you can depend on them to return each name's info from the master dataset!

 

This is by using some lookup functions such as VLOOKUP, INDEX/MATCH.

 

If so, please attach a sample of them to show you how to do that.

Highlighted

If you have the list of 2000 names somewhere in the workbook, you can add a helper column to the original data table with a formula along the lines of

 

=if(countif(Namelist!$A:$A,[@Name])>0,True,false)

 

So, look at the name in the current table and find it in the list on the other sheet. Return TRUE if found and FALSE if not.

 

Now you can use conditional formatting on your table, using a formula that looks at the value in the helper column. Select the table and, assuming the current cell is in row 2 the Helper column is in column Z, use a formula like this

 

=$Z2

 

The value of Z2 is either TRUE of FALSE, so you don't need a complex formula. Just make sure that the column reference has a $ sign and the row number has NO $ sign in front of it. Then select a fill color to highlight and apply the rule to your whole table.

 

With this approach, you can add more names to your list of 2000 and they will automatically be highlighted in the table.

 

Let me know if that works for you.

 

Highlighted

Hi Haytham,

 

Thanks for your response!

 

Please see the attached excel.

 

I have abridged my table here with ~1000 rows and 3 columns.

 

I need to find the 20 IDs highlighted in column 4 from column1.

 

How to use the index/match/vlookup in this case?

Highlighted
Solution

Hello,

in the following screenshot, I moved the list to column F and created a helper column in column D with the formula

 

=IF(COUNTIF(F:F,A2)>0,TRUE,FALSE)

 

in cell D2 and copied down. 

 

Then I selected the whole table from row 2 to the bottom and created a new conditional formatting rule with the formula

 

=$D2

 

I selected a yellow fill and confirmed all dialogs.

 

2018-04-02_09-01-55.png

Highlighted

Hi Ingeborg Hawighorst,

 

Worked really well!!! 

 

Thanks for saving my weekend, and a lot of time!!!

 

Best regards,

 

Viv

Highlighted

Hi Vivek,

 

Please note that you have to use two techniques, one for highlighting, and one for lookup.

@Ingeborg Hawighorst has explained the first one.

But you don't have to create a helper column, you can directly put that formula into the Conditional Formatting rule.

 

With regards to the second one, you can depend on VLOOKUP function, with the support of COLUMNS function as follows:

=VLOOKUP($E2,$A$2:$C$1000,COLUMNS($A$1:B$1),0)

Where:

$E2 is the lookup value;

$A$2:$C$1000 is the lookup range;

COLUMNS($A$1:B$1) is column index number;

0 represents FALSE which is turn on the exact match.

 

NOTE: I've used COLUMNS function in column index number argument to make the column index number expandable as you drag the formula to the right so that you don't have to change it manually for each column.

 

I've updated the workbook with the solution, please find it in the attachments.

 

Hope that helps

Haytham