Need Destination cell to ref origin cell, but w/ many variables..H

M

Ms.shopalot

I need help with a formula. I don’t know how to do a Macro so hopefully
someone can help me without me getting completely confused! I am going to get
somewhat specific because I don’t know how else to explain what I am needing
help with! Thanks so much in advance!

Here is my dilemma:

I have Column C set up as store numbers. However, I have Column L set up to
represent the County that the store is located. I need to have a way to
return L automatically depending on the store # that is inserted into column
C. This is going to be huge because I have 63 stores (Column C) and about 10
different counties (column L)! The below is what I started with but I don’t
know how to formulate this!

If column C1 has “7545†or “7567†then column L1 = “Baltimore County†but
if Column C1 has “2612†or “2646†then column L1 = “Montgomery County†(etc,
etc!)


HELP PLEASE!!!! Ahhhh!
 
R

Ron Coderre

Try something like this:

Put the XRef list of STORE_NUM and COUNTY into a separate list.

Example:
refs Col_Y Col_Z
Row_1 STORE_NUM COUNTY
Row_2 7545 Baltimore County
Row_3 7567 Baltimore County
Row_4 2612 Montgomery County
Row_5 2646 Montgomery County

Then.....if
C2: 7567

This formula returns the COUNTY associated with that STORE_NUM
L2: =VLOOKUP(C2,$Y$1:$Z$31,2,0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
M

Ms.shopalot

Hi Ron!

This looks managable! I'll try it and see if it works! Thanks SO much! :)

Ms.Shopalot
 
M

Ms.shopalot

I don't know what I am doing wrong! Everything makes sense to me except for
the part that is :$Z$31,2,0) What do those numbers represent? When I type in
the entire formula it returns #N/A
Everything else is set up exactly as you had laid it out...store num in
column Y, County in Column Z

Does that make sense? What am I doing wrong?

Ms.Shopalot
 
B

Billy Liddel

Points about VLookup

VLOOKUP(reference, Range to lookup, Column Offset, [True/False])

The reference is what you lookup (C2 in Ron's example)
The range with Table (Ron suggested columns X and Y for store number and
County) i.e Y1:Z31; adjust this range to suite your list.
Note that the reference for the table, the store number must be on the left
of the table and in ALPHABETICAL ORDER so sort the list when its complete

Ron has expressed the true/ false part as a number and will vllokup will
express #NA! if and exact match is not found. And it may not be found if the
list is not in store number.

Regards
Peter
 
Top