I was asked the following question by a colleague today.

I have two spreadsheets and I have to check which part number is listed on both. What’s the quickest way apart from: copy party number go onto other spreadsheet do CTRL find and paste part number and do find all?

I recommended the Match Function.

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

Lookup_value is the value you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.

Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.

If match_type is omitted, it is assumed to be 1.
Remarks

I cobbled this code together for someone at work. They wanted to unhide rows if some cells contained Yes.

The control cells were D4, D5 & D6. You should be able to reuse this code.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


Peter Noneley      Excel Function Dictionary Updated Oct 2009 – no macros or VBA code, smaller file size. The Excel Function Dictionary contains over 150 examples of functions.

via www.xlfdic.com Excel Function Dictionary 150 example functions and formula..

Click to Download