Look up function? Or IF?

[britt1229]

Hi there,

Trying to create a table to help me go through address information quicker, and create all Postal codes (FSA's specifially) into a group.

Ex: (for ontario, Canada)

K9A
K8A
L9L
M1B
etc....

I want to create in excel a list of certain postal codes that would be for example, southern Ontario or northern Ontario etc., depending on the postal code listed....


Does that make sense? Am I expecting too much from my program?

thanks!!

B

 

[Joe4]

Take a look at the VLOOKUP function in Excel help. This should you how you can lookup a value from a table and return a related value.

 

[britt1229]

okay, still a little confused.

Here is a better description of what I'm trying to do.

I have a table I created that looks as follows:

<table border="0" cellpadding="0" cellspacing="0" width="432"><col style="mso-width-source:userset;mso-width-alt:1426;width:29pt" width="39"> <col style="mso-width-source:userset;mso-width-alt:9252;width:190pt" width="253"> <col style="mso-width-source:userset;mso-width-alt:5120;width:105pt" width="140"> <tbody><tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:29pt" height="21" width="39">FSA</td> <td class="xl68" style="width:190pt" width="253">CITY</td> <td class="xl68" style="width:105pt" width="140">AREA</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">L0C</td> <td class="xl64">Rural</td> <td class="xl65">West CENTRAL</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">L0H</td> <td>Rural</td> <td class="xl67">North CENTRAL</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">L9P</td> <td>UXBRIDGE</td> <td class="xl67">Eastern ONT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">L9L</td> <td>PORT PERRY</td> <td class="xl67">Northern ON
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">L0B</td> <td>Rural
</td> <td class="xl67">West Central
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">L0A</td> <td>Rural</td> <td class="xl67">EAST CENTRAL
</td> </tr> </tbody></table>
Now, that is what the table looks like, where I'm trying to apply those AREAS, I have another table with just the addresses & FSA's listed. So I'm trying to determine based on the "FSA" what would the "area" be, in order to group all addresses by the "area".

Does that make sense? Any help would be great!

Thanks!

B

 

[Joe4]

So based on the "FSA" value, you are trying to LOOKUP the "Area", right?
That is EXACTLY what the VLOOKUP function is used for.
What you have posted is your lookup table (the list of values to match on, lookup, and return).

Did you take a look at the help on the VLOOKUP function, at the example they posted?
Are you having a hard time applying it?

 

[britt1229]

Hey Joe,

yes I do have it working on one sheet, but how would I do it if the table is in another sheet?

and can I replace the "lookup_value" with the Cell info i.e. A14 in order to copy the formula down the column of the entire list of addresses?

thank you!

 

[Joe4]

yes I do have it working on one sheet, but how would I do it if the table is in another sheet?

Two ways:

1. Include the sheet name reference in your formula, i.e.
=VLOOKUP(C14,Sheet2!A1:B3,2,0)

2. Name your lookup range, and use that named range, i.e.
=VLOOKUP(C14,MyRange,2,0)

and can I replace the "lookup_value" with the Cell info i.e. A14 in order to copy the formula down the column of the entire list of addresses?

Not quite sure I understand what you are asking here. Perhaps you are having issues with the ranges changing as you copy the formulas down. That can addressed by using absolute range references, or by using named ranges like I showed above.

If that is not what you are asking, please clarify, maybe with a detailed example.

 

[britt1229]

Hey Joe,

Thank you so much for all of your help, I'm doing great so far, figured out most of the lookup funtions, with your help of course!

Next issue, I've copied the formula down the column where the looked up value will be, but the table array cells (i.e. A2:C532) seem to go to the next value when i copied them down.

My formula looks like this: =VLOOKUP(Y2, Sheet4!A2:C532, 3, FALSE)

how would I lock the table array part of the formula, but keep the lookup_value part changing to Y3, Y4, Y5 etc.?

Thanks again!

B

 

[Joe4]

Yes, that is what I was asking you above:

Perhaps you are having issues with the ranges changing as you copy the formulas down. That can addressed by using absolute range references, or by using named ranges like I showed above.

You can lock row/column range references down by placing a "$" in front of them (this is known as "Absolute Range Referencing").

So if we wanted to just lock down the rows, that would look like this:
=VLOOKUP(Y2, Sheet4!A$2:C$532, 3, FALSE)

To lock both the cells and rows, that would look like this:
=VLOOKUP(Y2, Sheet4!$A$2:$C$532, 3, FALSE)

However, as I mentioned above, if you name your lookup table range (referred to as "Named Ranges"), then you never have to worry about this, as you just use the range name.

Naming ranges is pretty easy, but how to do it differs slightly based on what version of Excel you are using.