I've an Excel sheet with almost 30,000 rows and I have a list to be searched within the Excel of size 100 values. I don't want to search manually each time.
How can I perform a search of all these 100 values at a single go?
Asked
Active
Viewed 122 times
0
-
1Some information about the data to be search (layout, etc) would be great. Otherwise, google for `vlookup` – David K-J Feb 19 '15 at 11:42
-
What result do you want from the values - a count or sum? – Mark Fitzgerald Feb 19 '15 at 11:45
-
Highlight the found ones. – RaMs_YearnsToLearn Feb 19 '15 at 11:46
-
Are the values numbers or text? – Gary's Student Feb 19 '15 at 12:01
1 Answers
0
Assuming your longer list is in ColumnA and your shorter one is a named range My100List, in B1 and copied down to suit:
=IF(ISERROR(MATCH(A1,My100List,0)),"","listed")
then filter ColumnB to select listed.
Edit re comment:
Select ColumnA and apply a Conditional Formatting formula rule of:
=MATCH(A1,My100List,0)>0
with fill colour of choice.
pnuts
- 58,317
- 11
- 87
- 139