PDA

View Full Version : Excel LOOKUP() question...

12-08-2006, 03:28 PM
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

Shagnasty
12-08-2006, 03:35 PM
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)

12-08-2006, 03:40 PM
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.

Shagnasty
12-08-2006, 03:45 PM
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.

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)

Shagnasty
12-08-2006, 03:47 PM
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)

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

If E is the column to return.