Microsoft Excel is a great tool to organize multiple degrees of data. It lets you manage data easily and search excel for particular data just by using simple search button. If you want to know How to search in Excel , then there are some functions that lets you do that but only to some extent. Here are some of them -
The following is an example of LOOKUP formula syntax:
The following formula finds Mary's age in the sample worksheet:
Note The LOOKUP function requires that the table be sorted.
The following is an example of VLOOKUP formula syntax:
The following is an example of the syntax that combines INDEX and MATCH to produce the same results as LOOKUP and VLOOKUP in the previous examples:
The following formula finds Mary's age in the sample worksheet:
Note If none of the cells in Lookup_Array match Lookup_Value ("Mary"), this formula will return #N/A.
The following is an example of syntax that combines OFFSET and MATCH to produce the same results as LOOKUP and VLOOKUP:
LOOKUP()
The LOOKUP function finds a value in a single row or column and matches it with a value in the same position in a different row or column.The following is an example of LOOKUP formula syntax:
=LOOKUP(Lookup_Value,Lookup_Vector,Result_Vector)
The following formula finds Mary's age in the sample worksheet:
=LOOKUP(E2,A2:A5,C2:C5)
The formula uses the value "Mary" in cell E2 and finds "Mary" in the lookup vector (column A). The formula then matches the value in the same row in the result vector (column C). Because "Mary" is in row 4, LOOKUP returns the value from row 4 in column C (22).Note The LOOKUP function requires that the table be sorted.
For more information about the LOOKUP function, click the following article number to view the article in the Microsoft Knowledge Base:
324986 (http://support.microsoft.com/kb/324986/ ) How to use the LOOKUP function in Excel
VLOOKUP()
The VLOOKUP or Vertical Lookup function is used when data is listed in columns. This function searches for a value in the left-most column and matches it with data in a specified column in the same row. You can use VLOOKUP to find data in a sorted or unsorted table. The following example uses a table with unsorted data.The following is an example of VLOOKUP formula syntax:
=VLOOKUP(Lookup_Value,Table_Array,Col_Index_Num,Range_Lookup)
The following formula finds Mary's age in the sample worksheet: =VLOOKUP(E2,A2:C5,3,FALSE)
The formula uses the value "Mary" in cell E2 and finds "Mary" in the left-most column (column A). The formula then matches the value in the same row in Column_Index. This example uses "3" as the Column_Index (column C). Because "Mary" is in row 4, VLOOKUP returns the value from row 4 in column C (22). For more information about the VLOOKUP function, click the following article number to view the article in the Microsoft Knowledge Base:
181213 (http://support.microsoft.com/kb/181213/ ) How to Use VLOOKUP or HLOOKUP to find an exact match
INDEX() and MATCH()
You can use the INDEX and MATCH functions together to get the same results as using LOOKUP or VLOOKUP.The following is an example of the syntax that combines INDEX and MATCH to produce the same results as LOOKUP and VLOOKUP in the previous examples:
=INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)
The following formula finds Mary's age in the sample worksheet:
=INDEX(A2:C5,MATCH(E2,A2:A5,0),3)
The formula uses the value "Mary" in cell E2and finds "Mary" in column A. It then matches the value in the same row in column C. Because "Mary" is in row 4, the formula returns the value from row 4 in column C (22).Note If none of the cells in Lookup_Array match Lookup_Value ("Mary"), this formula will return #N/A.
For more information about the INDEX function, click the following article number to view the article in the Microsoft Knowledge Base:
324988 (http://support.microsoft.com/kb/324988/ ) How to use the INDEX function to find data in a table
OFFSET() and MATCH()
You can use the OFFSET and MATCH functions together to produce the same results as the functions in the previous example.The following is an example of syntax that combines OFFSET and MATCH to produce the same results as LOOKUP and VLOOKUP:
=OFFSET(top_cell,MATCH(Lookup_Value,Lookup_Array,0),Offset_Col)
This formula finds Mary's age in the sample worksheet: =OFFSET(A1,MATCH(E2,A2:A5,0),2)
The formula uses the value "Mary" in cell E2 and finds "Mary" in column A. The formula then matches the value in the same row but two columns to the right (column C). Because "Mary" is in column A, the formula returns the value in row 4 in column C (22). For more information about the OFFSET function, click the following article number to view the article in the Microsoft Knowledge Base:
But when it comes to find duplicates in multiple worksheets excel search fails alltogether. I found this software Pretty Usefull when you want search for duplicates in excel and highlight them. Try this software. I bought it from www.techmate.in . I list the software below for your refference. see for yourself . To buy it contact at the mail address below.
TechMate Excel Solution v1.0
TechMate Excel Solution v1.0 lets you manage your hundreds of worksheets in an excel file with ease. It Searches over multiple worksheets and highlights duplicate data found in an Excel Workbook for a single cloumn. It also has the functionality to count the number of sheets in a single workbook.
Here is what you get with TechMate Excel Soltuion -
1. Duplicate Data Finder : You set the column number and the color. It will search through all your sheets for any duplicate data it finds in the excel file and highlights duplicate cells with color of your choice. It saves the new excel file with duplicate data highlighted into a new file which you can choose where to save,
2. Sheet Counter : Ever faced the problem of counting how many sheets are there in your huge excel workbook ? with this you can count the sheet number with just a click of a button.
Here is Why You Should Consider it -
1. There is NO Other Software on the market that does exactly what it does.
2. This softwares saves your survey or statistics reports files from having any duplicated content and thus makes your work smoother.
3. When I say it works at a blazing speed trust me on this. It searches through your excel file with an unmatched speed. So Within moments you can get a check on your huge Excel file .
OS Requirements : Windows Xp or Higher
Processor Requirements : Pentium 4 2.4 Ghz Or Better.
[it will run on lower CPUs with a degraded performance]
Other Requirements : .NET Framework 3.5 Or Higher
Single Licence Price : 39$
TO Buy The Software Contact Us By Clicking Here.
Or You can send us an E-Mail at support@techmate.in
Here is what you get with TechMate Excel Soltuion -
1. Duplicate Data Finder : You set the column number and the color. It will search through all your sheets for any duplicate data it finds in the excel file and highlights duplicate cells with color of your choice. It saves the new excel file with duplicate data highlighted into a new file which you can choose where to save,
2. Sheet Counter : Ever faced the problem of counting how many sheets are there in your huge excel workbook ? with this you can count the sheet number with just a click of a button.
Here is Why You Should Consider it -
1. There is NO Other Software on the market that does exactly what it does.
2. This softwares saves your survey or statistics reports files from having any duplicated content and thus makes your work smoother.
3. When I say it works at a blazing speed trust me on this. It searches through your excel file with an unmatched speed. So Within moments you can get a check on your huge Excel file .
OS Requirements : Windows Xp or Higher
Processor Requirements : Pentium 4 2.4 Ghz Or Better.
[it will run on lower CPUs with a degraded performance]
Other Requirements : .NET Framework 3.5 Or Higher
Single Licence Price : 39$
TO Buy The Software Contact Us By Clicking Here.
Or You can send us an E-Mail at support@techmate.in
No comments:
Post a Comment