need help with lookup function

W

wii

WHEN USING VLOOKUP.....


I am looking up an item in 2 different tables, however sometimes th
item is not in both tables.

When the one table does not have the item, excel returns a (n/a
entry.

How do I program it to use a default value of 0 when no record i
found???


=VLOOKUP(A2,WAUKREF!A2:S1000,18,0)
+VLOOKUP(A2,WWREF!$A$2:$S$1000,18,0)

I THOUGHT MAYBE "0" IN THE ARGUMENT, BUT IT IS NOT WORKING.

IT IS BEING USED TO AUTO CALCULATE THE COMBINATION OF THE TWO FEILDS

ANY HELP WOULD BE GREATLY APPRECIATE
 
P

Pete_UK

Try this:

=if(isna(VLOOKUP(A2,WAUKREF!A2:S1000,18,0)),0,VLOOKUP(A2,WAUKREF!A2:S1000,18,0))

+if(isna(VLOOKUP(A2,WWREF!$A$2:$S$1000,18,0)),0,VLOOKUP(A2,WWREF!$A$2:$S$1000,18,0))

Should give you what you want - the lower case will convert to upper
case once you type it in.

Hope this helps.

Pete
 
Top