Lookup Help!!!!

[jpr1877]

I have a formula issue that i can't seem to resolve, and I'm sure it's an easy fix to you guys. I have a base list of zip codes (w) for a territory (say 100 of them). And next to them in a blank column (x) I am copying and pasting representative numbers that indicate a number of businesses within that zip code. However, in the source list, if there are zero businesses within a zip, that zip is not included in the list. So, the resulting list of zips is shorter than the base list, thus requiring an adjustment. I have been editing it manually, but it is very tedious.

What I have been doing is adding two more columns (y and z) and pasting in the zips and corresponding figures next to an empty column (x) where I want the nice and neat final figures to go. My question is, what is the formula to do this automatically? As you can see, I would like 60004 to have 3 copied into x, 60005 to have a zero or blank, 60007 to have a 16, and so forth? I've rambled on long enough. Any thoughts?
w x y z
60004 60004 3
60005 60007 16
60007 60008 1
60008 60010 8
60010 60012 1
60012 60013 5
60013 60014 5
60014 60015 1
60015 60016 3
60016 60018 3
60018 60021 1
60021 60025 1

 

[Brian from Maui]

In AA1, enter

=VLOOKUP(W1,$Y$1:$Z$100,2,0)

In X1 enter and copied down

=IF(ISNA(AA1),"",AA1)

 

[FinancialAnalystKid]

in column X use a if statement with a vlookup.

If your source list is a workbook and ZIP is in Column A and Business Count in Column B add this formula to column X in your BASE list.

=IF(ISERROR(VLOOKUP(A1,SourceList!$a$100:$b$100,2,FALSE)),0,VLOOKUP(A21,SourceList!$C$21:$D$22,2,FALSE))

 

[jpr1877]

Worked like a champ! Thank you very much. Just out of my curiosity, can you explain to me what the 2,0 represent in the vlookup formula? Thanks.

Jeff

 

[Brian from Maui]

Worked like a champ! Thank you very much. Just out of my curiosity, can you explain to me what the 2,0 represent in the vlookup formula? Thanks.

Jeff

It's the column number to return from the lookup table. The 0 or False looks for an exact match in the lookup column and returns #N/A if none is found. I don't know which formula you're using but try to avoid ISERROR. You're only trapping the #N/A error so if you're using FAK's formula, change ISERROR to ISNA. ISERROR hides ALL errors including syntax errors.