Items Not Found in Lookup Tables

[bdicarlo1]

I'm setting up Lookup Tables for the first time, and have gotten myself

into some trouble along the way. If an item is not found in my Lookup
Table, it seems to pick up the closest item alphabetically. How do I
prevent this from happening? Is there anything I could put in my
Lookup Table that would be a catch all for items not found, and would
act as an alert to me that the table needs to be updated with new
values? Thanks!

 

[Dave Peterson]

There's a fourth argument that specifies that you want an exact match:

=vlookup(a1,sheet2!a:b,2,false)

That False (or 0) tells excel to look for an exact match.

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html

 

[Pete_UK]

It would help if you posted your formula so we could give some direct
advice, but on a general level ...

There is a fourth (optional) parameter that can be used with VLOOKUP -
True or False (or 1 or 0), which enables you to look for an exact match
(if set to False or 0). If omitted, or set to True, then VLOOKUP will
report on the highest value which is less than the search value,
assuming the data in the table is sorted.

However, if you look for an exact match and there isn't one in the
table, the function will return an error message #N/A - this can be
trapped using ISNA to give you a reminder message. So, you may have a
formula along the lines of:

=IF(ISNA(VLOOKUP(A1,table,2,0)),"Missing data",VLOOKUP(A1,table,2,0))

which means if there is no exact match of A1 in table (a named range)
then report "Missing data", otherwise fetch the corresponding data from
column 2 of table.

Hope this helps.

Pete

 

[bdicarlo1]

Here is the formula I am using: =LOOKUP(E109,LookUp!A:A,LookUp!B:B) -
Should I be using something more complex?

 

[Dave Peterson]

Maybe
=vlookup(e109,lookup!a:b,2,false)

Or a variation of Pete's suggestion.

 

[aaron.kempf]

yeah
you should be using a database; and then you could use referential
integrity.

spreadsheets are for babies and retards

-Aaron