# Excel LOOKUP() question...

**URL:** <https://boards.straightdope.com/t/excel-lookup-question/383401>\
**Category:** Factual Questions\
**Created:** [December 8, 2006, 9:28pm UTC](https://boards.straightdope.com/t/excel-lookup-question/383401 "2006-12-08T21:28:04Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rattlehead02](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rattlehead02/32/3944_2.png) [@Rattlehead02](https://boards.straightdope.com/u/Rattlehead02)\
**Post date:** [December 8, 2006, 9:28pm UTC](https://boards.straightdope.com/t/excel-lookup-question/383401/1 "2006-12-08T21:28:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [December 8, 2006, 9:35pm UTC](https://boards.straightdope.com/t/excel-lookup-question/383401/2 "2006-12-08T21:35:54Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Rattlehead02](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rattlehead02/32/3944_2.png) [@Rattlehead02](https://boards.straightdope.com/u/Rattlehead02)\
**Post date:** [December 8, 2006, 9:40pm UTC](https://boards.straightdope.com/t/excel-lookup-question/383401/3 "2006-12-08T21:40:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [December 8, 2006, 9:45pm UTC](https://boards.straightdope.com/t/excel-lookup-question/383401/4 "2006-12-08T21:45:51Z")

</div>

[QUOTE=GildedLily]  
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.  
[/QUOTE]

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)

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [December 8, 2006, 9:47pm UTC](https://boards.straightdope.com/t/excel-lookup-question/383401/5 "2006-12-08T21:47:07Z")

</div>

[QUOTE=Shagnasty]  
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)  
[/QUOTE]

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

If E is the column to return.

---

<div class="post-metadata">

**Author:** ![Rattlehead02](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rattlehead02/32/3944_2.png) [@Rattlehead02](https://boards.straightdope.com/u/Rattlehead02)\
**Post date:** [December 8, 2006, 10:07pm UTC](https://boards.straightdope.com/t/excel-lookup-question/383401/6 "2006-12-08T22:07:01Z")

</div>

Shagnasty, that worked perfect, thanks!