Excel LOOKUP() question...

Factual Questions
1

I have this function in cell A2 of sheet1: =LOOKUP(B2,maillist2!D:D,maillist2!E:E)

sheet1!B2 contains builders, it’s a unique list. maillist!D:D contains a similar unique list of builders, though it contains far more than sheet1!B2. Maillist!D:D contains the street for each builder.

The function works fine for builders who are in sheet1 and maillist. But if a builder is in sheet1, but NOT maillist, LOOKUP seems to put in an arbitrary address.

How can I make it so that sheet1!A2 is blank (or #N/A, or anything but an arbitrary street) when there is no match in maillist!D:D?

Thanks,

Jeff

2

I think the very last part of your formula should be:

,FALSE)

That tells it not to do a range lookup and instead only return exact matches.

A similar formula I have is:

VLOOKUP($A1,‘SampleDate’!$A$2:$E$10000,4,FALSE)

3

Damn the emoticons! Those are all D:D.

Shagnasty, I tried =LOOKUP(B2,maillist2!D:D,maillist2!E:E, FALSE) but that gave me an “Too Many Arguments” error.

4

Lookup is an old function that has been largely replaced.

I think you can switch it to a VLookup:

=VLOOKUP(B2,‘maillist2’!$D$1:$E$10000, FALSE)

5

Sorry
=VLOOKUP(B2,‘maillist2’!$D$1:$E$10000,2, FALSE)

If E is the column to return.

6

Shagnasty, that worked perfect, thanks!