Vlookup

[Biffarama]

In my worksheet I'm trying to fill in the Zip Code column based on the City and State listed for each row. I downloaded a zip code database and added it to my worksheets a new tab in the hopes that I'd be able to use the VLOOKUP formula to populate the field from it, but I can't seem to figure it out. Looking for any help I can get with it.

Let me know if you have any questions or I haven't been clear.

Thanks in advance for your help.

Troy

 

[Nine Zero]

Re: Need help with VLOOKUP

Hey,

Could you give us a brief example of what the columns and rows look like for each worksheet

just maybe 1 row with all columns from each

 

[Biffarama]

Re: Need help with VLOOKUP

Sure thing.

[TABLE="width: 998"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Location[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip Code[/TD]
[/TR]
[TR]
[TD]1st Franklin Financial[/TD]
[TD]1ffc - Marketing Demo[/TD]
[TD]Toccoa[/TD]
[TD]GA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 696"]
<!--StartFragment--> <colgroup><col width="87" span="8" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 87"]Zipcode[/TD]
[TD="class: xl64, width: 87"]ZipCodeType[/TD]
[TD="class: xl64, width: 87"]City[/TD]
[TD="class: xl64, width: 87"]State[/TD]
[TD="class: xl64, width: 87"]LocationType[/TD]
[TD="class: xl64, width: 87"]Lat[/TD]
[TD="class: xl64, width: 87"]Long[/TD]
[TD="class: xl64, width: 87"]Location[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]705[/TD]
[TD="class: xl64"]STANDARD[/TD]
[TD="class: xl64"]AIBONITO[/TD]
[TD="class: xl64"]PR[/TD]
[TD="class: xl64"]PRIMARY[/TD]
[TD="class: xl64, align: right"]18.14[/TD]
[TD="class: xl64, align: right"]-66.26[/TD]
[TD="class: xl64"]NA-US-PR-AIBONITO[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[Nine Zero]

Re: Need help with VLOOKUP

Is it possible for you to move your column A (which is your zipcode) over to in between your 'State' column and your 'LocationType' column......

this is because you need to remember that VLOOKUP works from left to right... so we need your result to the right of your search criteria

Is this possible?

Also .. would you mind inserting a helper column? would this be an issue>? it can be hidden if desired

 

[Biffarama]

Re: Need help with VLOOKUP

Yes to both, where should I put the helper column?

 

[Nine Zero]

Re: Need help with VLOOKUP

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Location[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zipcode[/TD]
[/TR]
[TR]
[TD]1st Franklin Financial[/TD]
[TD]1ffc - Marketing Demo[/TD]
[TD]Toccoa[/TD]
[TD]GA[/TD]
[TD]XXXXXX
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ZipCodeType[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Helper[/TD]
[TD]Zipcode[/TD]
[TD]LocationType[/TD]
[TD]Lat[/TD]
[TD]Long[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]STANDARD[/TD]
[TD]AIBONITO[/TD]
[TD]PR[/TD]
[TD]XXXXXX
[/TD]
[TD]705[/TD]
[TD]PRIMARY[/TD]
[TD]18.14[/TD]
[TD]-66.26[/TD]
[TD]NA-US-PR-AIBONITO[/TD]
[/TR]
</tbody>[/TABLE]


Ok perfect so first re arrange your zipcode file in the following order... i basically moved the first column in between the 'State' column and the 'LocationType' column... then I added a 'Helper' column to the left of the zipcode column

These will be your formulas assuming all of your data begins in cell A1

XXXXXX ------ =CONCATENATE(B2,C2) ......and drag it all the way down to the end of your data

XXXXXX ------ =VLOOKUP(CONCATENATE(C2,D2),[Book2]Sheet1!$D:$E,2,0)) ...... (change "Book2" to the name of your book with the zipcodes in them and change "Sheet1" with the name of the sheet in that book. and drag this down as well and thats it!

Let me know if I need to explain anything else!

 

[Nine Zero]

Re: Need help with VLOOKUP

Wow.... I just now realized I was answering two different questions and i got mixed up...... my fault 100%

The above will work.... Thats not the issue

the issue is this one will work also without changing anything

=INDEX([Book2]Sheet1!$A$2:$A$10,MATCH($C2&$D2,[Book2]Sheet1!$C$2:$C$10&[Book2]Sheet1!$D$2:$D$10,0))

this is an array formula and will only work if you do CTRL + SHIFT + ENTER instead of just Enter

just change the locations of the Book2 and Sheet1 like the example above......and this is all assuming all your data for both sheets begins at A1

I am truly sorry for the mix up!

 

[Biffarama]

Re: Need help with VLOOKUP

Thanks very much for this Nine Zero. The formula for XXXXXX worked great, but not the one for XXXXXX and I think it has to do with the set up of my workbook, so below I pasted a screenshot of the setup to see if this helps you at all?

 

[Nine Zero]

Re: Need help with VLOOKUP

your screenshot does not appear unfortunately but i am assuming it is the VLOOKUP one that didnt work....

=VLOOKUP(CONCATENATE(C2,D2),[Book2]Sheet1!$D:$E,2,0))

this will only work if the following are done

make sure that your data begins at cell A1 and the columns are in the same order you described above.

change [Book2]Sheet! to whatever your workbook is called and whatever your sheet is called ... leave the [ ] and ! symbols alone just change the text

make sure your data in the zip file also begins at A1 and the columns are in the order mentioned above

 

[Biffarama]

Re: Need help with VLOOKUP

I just realized that my reply wasn't very helpful. Yes, it's the XXXXXX that I'm having trouble with.

I have two tabs on my workbook, Dealer - Locations (where XXXXXX is) and free-zipcode-database-Primary (where XXXXXX is)