Missing Value Lookups In MS Excel

[shyam131]

I am using Excel 2002. I have a master list of companies and two seperate lists (LIST1, LIST2) of the companies on another spreadsheet. All the companies in the seperate lists (LIST1, LIST2) are not neccesarily in the master list. How can I look up which companies in the master list are missing from LIST1 and LIST2.

Example

Master List LIST1 LIST2
Comp 1 Comp2 Comp4
Comp 2
Comp 3
Comp 4

So I would want a fuction/formula that returns the information that Comp 3 and Comp 1 are missing from LIST1 and LIST2, and are in the Master List

Any ideas?

 

[SkipVought]

Hi,

You can use the MATCH function -- when the function returns the N/A error, there is no match

Assuming that your master list is in A1
then in B...

=match(A1,LIST1,0)

and in C

=match(A1,LIST2,0)

assuming that LIST1 & LIST2 are named ranges





Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[shyam131]

Using the match function works, in the sense that it gives me the desireed information, but I want something a little more savy, in the sense that it would only return the names of the missing companies. Right now I have a VLOOKUP that accomplishes that samething as the above match function solution.

 

[SkipVought]

A function returns ONE value.

You need to see a range of values.

So you use the MATCH or some other lookup function and filter the list on the N/A's and you have your list, no?

Otherwise, you have to write some VBA code. Are you up to that?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com