V-lookup formula help

[royhern]

Guys, first of all i have received some help on this issue before but i am looking for another answer without having to do too much to my current worksheet since the data i will be using is being paste from another data source.

Team consider this. on cell AS6 i want to be able to key in a region number,
for example A0, A1 A2 etc. In this case i am using A0

On cells AQ11 to AQ55 the rows are numbered 1 to 45.
On cells AR11 to AR 55, i have this formula =TEXT(AQ11,0)&TEXT($AS$6,0)
On cells AS11 to AS55, i have this formula =IF(ISERROR(VLOOKUP(AR11,Store,7,FALSE)),"",VLOOKUP(AR11,Store,7,FALSE))

The issue that i have is when i key the region code (A0) nothing happens and i want to have a list of all stores for that region that starts with A0

Below is the worksheet:
******** ******************** ************************************************************************>

Microsoft Excel - CustSurvey4.xls ___Running: xl97 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
AR6AR11AS11AQ12AR12AS12AQ13AR13AS13AQ14AR14AS14AQ15AR15AS15 =
	AQ	AR	AS	AT
4	*	*	Enter*Region	*
5	*	*		*
6	DIV	A0	A0	*
7	*	*		*
8	*	*	*	*
9	*	*	*	*
10	*	*	STORE	Dist
11	1	1A0	*	*
12	2	2A0	*	*
13	3	3A0	*	*
14	4	4A0	*	*
15	5	5A0	*	*
Week *

[HtmlMaker 2.20BETA] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

This is the source sheet named store. The search is being done in column
G and reports the store number which is in column H. For instance when i want to search for all stores in region A0 i should get 18 stores and the first store should be 6464, and if want to search for region A1 i should get store 2329 as the first store and so on...


******** ******************** ************************************************************************>

Microsoft Excel - CustSurvey4.xls ___Running: xl97 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
G2G3G4G5G6G7G8G9G10G11G12G13G14G15G16G17G18G19 =
	F	G	H	I
1	*	*	Store	Dist
2	1	1A0	6464	A0A
3	2	2A0	6531	A0A
4	3	3A0	3530	A0B
5	4	4A0	5460	A0B
6	5	5A0	5617	A0B
7	6	6A0	3013	A0C
8	7	7A0	714	A0D
9	8	8A0	3090	A0D
10	9	9A0	4569	A0D
11	10	10A0	1419	A0E
12	11	11A0	6480	A0E
13	12	12A0	5235	A0F
14	13	13A0	1494	A0G
15	14	14A0	2924	A0H
16	15	15A0	4654	A0H
17	16	16A0	2573	A0I
18	17	17A0	3105	A0I
19	18	18A0	2193	A0J
20	*	*	2329	A1A
21	*	*	6696	A1B
22	*	*	1014	A1C
Stores *

[HtmlMaker 2.20BETA] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


i haved use this method before and it works but for some reason is not working now thinking that probably i have an issue with one of the formulas or the way the search is being done.

[plettieri]

Hi:
Just a quick question ...did not see the parameters for the range "store"...?
plettieri

[royhern]

Plettieri, i don't understand what you mean by parameters, i guess this is my problem. if you look at the second image it shows columns F, G, H, I. I guess the parameter is to search column G and list all stores in H when G is 1A0, 2A0, 3A0 etc.. The result needs to be listed starting in AS11 to AS55.

I need a formula that i can place in cell AS11 that will look for those stores and i can drag it down to AS55.

Hope this info helps..

[Ian Mac]

Hello,

You don't need to post to a new thread,

Anyway, how about

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
B2B3G3H3B4G4H4B5G5H5B6G6H6B7G7H7B8G8H8B9G9H9B10G10H10 =
	A	B	C	D	E	F	G	H
1	*	*	Store	Dist	*	*	*	*
2	1	1A0	6464	A0A	*	*	A0	*
3	2	2A0	6531	A0A	*	*	6464	714
4	3	3A0	3530	A0B	*	*	6531	1419
5	4	4A0	5460	A0B	*	*	3530	1494
6	5	5A0	5617	A0B	*	*	5460	2193
7	6	6A0	3013	A0C	*	*	5617	2573
8	7	7A0	714	A0D	*	*	3013	2924
9	8	8A0	3090	A0D	*	*	714	3013
10	9	9A0	4569	A0D	*	*	3090	3090
Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Any help?

[royhern]

Any other ideas using all above criterias. Tried Ian's suggestion and got some answers but not what i was looking for. Thanks!!

As i placed A0 in cell AS6 , i need a formula in cell AS11 to AS55 that will search the tab "store" from Column G to I , you decide. The formula should generate a list of stores in AS11 to AS55. See below to view needed results
as it should appear in my worksheet. If i typed A1 in cell AS6 i should get another list of stores etc, etc...

Store

6464
6531
3530
5460
5617
3013
714
3090
4569
1419
6480
5235
1494
2924
4654
2573
3105
2193

thanks for your support...

[GorD]

Just a couple of thoughts

VLOOKUP(AR11,Store,7,FALSE).

This returns the value in the 7th column along in the range you have named "store" - does this range contain 7 columns?
Also the lookup value must be in the left most column of the range named store - again does your data comply with this.

[GorD]

Another thought. your original post, says "nothing happens" but the formula instructs that if an error is found return a blank - it might be an idea to remove that part of the formula to see what kind of error value is returned ie
#name, value etc

[plettieri]

Hi:

in your formula =IF(ISERROR(VLOOKUP(AR11,Store,7,FALSE)),"",VLOOKUP(AR11,Store,7,FALSE))


The parameter that I was looking for are the specifics of the named range "Store"...such as ae1:af50...or something like that....


as Gord notes the 7th column in the range....that what I am trying to check for...

plettieri

[Ian Mac]

Might be an idea to say in what way it wasn't what you were looking for.

I don't think you'll be able to use VLOOKUP for what you need!

[royhern]

Plettieri, the range to check for then is G1:H342 per the second image above.

thank you...

Roy