Create a list with VLOOKUP?

[artistapart]

Page 1 is presentation- I have a TON of data on a Page 2, Columns A:D down to row 758. Column A is all months- so there could be 30-75 entries of "January", then February and so on.

january| data1
january| data2
january| data3
february| data1
february| data2

On page 1, I have a Validation dropdown menu in cell A1 (the months), then a header in cell A2, then the list should begin. In cell A3 I have the following:
=VLOOKUP(A1,'page 2'!A:D,2,FALSE)

This works- its looking at A1, finding it on page 2, then printing whats next to it in column B of the same row. Great! now the hard part:

I need the next cell down to have the same effect- look at A1, find it on page 2, then print whats next to it in Column B- but I need it to be the next one down until it runs out of matches.
So, is there a way to make excel say A1 matches- here's B1. Then say A1 matches, but we did that, so does A2 match? yes: then here's B2 (or No, so N/A)

[6StringJazzer]

Put this formula in A3:

Please Login or Register  to view this content.

After you enter the formula, do not press ENTER. This is an array formula. Instead, press CTRL+SHIFT+ENTER. If you did it correctly you will see the formula inside {braces} in the formula box.

Then fill down as many rows as needed.

[6StringJazzer]

Change the 1 to 22 range to be whatever rows you are actually using (change in 3 places)

[artistapart]

this is almost it!

There's two problems- and I think I can figure out the second if you can explain the first.

It is printing, but its printing data that is 3 cells over and 3 cells down from where it catches the match. What i mean is if column A is all dates (which it is), then column D is all different numbers- when it catches the match in column A, it prints the number in D3 rather than D1. The second issue is when I fill down it isn't continuing itself to verify that it would go on to print D4, D5, D6 etc

Im trying to understand your formula, can you tell me if this layman'd down version is correct? cos I suspect its just a small tweak or something I messed up along the way?

Please Login or Register  to view this content.

Edit: appears its doing the 3 over thing cos of the reference of A3 in the INDEX section- bouncing it 3 down. So, the index is creating a table of its own. when I changed that to A1:A760 it started printing line one.

as for the second problem, you can't seem to flow the function down, because it +1's every cell in the code, which is fine except it also changes the reference cell as well (after =)

Edit2: with the modifications described it is, from best I can tell, printing all of the column- regardless of matching the dates or not- right direction, wrong execution so far, but I believe its still my understanding of the string.

[artistapart]

Change the 1 to 22 range to be whatever rows you are actually using (change in 3 places)

Caught that, thank you-- I don't understand the section ROWS(A$3:A3) what is that referencing?

edit: appears thats referencing the target cell!

[6StringJazzer]

I can't tell if you finally got it working or not. If not, I suggest you attach your file then I can update your actual file to make sure I have it working. Then I can explain how it works.

[artistapart]

Sorry for delay, I didn't want to bother anyone and try to figure it out on my own. I DID getting it working but I've one small glitch I can't seem to get past. It starts the list where I need it to based upon the criteria selected, but it doesn't stop there.

What I mean is, if I search based on criteria 'February' and the data its polling is as follows:

January | Potato
January | Tomato
January | Lettuce
February | French Fry
February | Onion Ring
March | Toast
March | Bagel
March | Croissant

The data it spits out will be:

French Fry
Onion Ring
Toast
Bagel

and so on. When the lines that don't match 'February' should just be blank. hopefully that makes sense- I know there's something stupid i'm missing, I just can't find it! Thanks for all your help!!

[6StringJazzer]

Please attach your file with the above data and the formulas you are using.

[artistapart]

Attached.

In the sample, we select district by dropdown in A2, which should generate the stores in cells A9+. What is happening is its repeating the first store it finds.

second problem, when you select store in cell D2, it should generate the employees of that store in cells D9+, this it IS doing properly, however it doesn't STOP when it runs out of employees- its simply continuing on from the master list of data.

Thanks for your continued help!

[JeteMc]

A solution for the first problem is the following array entered formula* :

Please Login or Register  to view this content.

*1) Paste the formula into Summary!A10,
2) press F2,
3) press Ctrl, Shift and Enter simultaneously,
4) copy down as far as needed.
As to the second problem I didn't find corresponding codes in Summary!D3 or Summary!D5 and Key!K:K, so I can't say how to fix the problem.
Let me know if you have any questions.

[artistapart]

what a difference a $ makes. Working now. +++++ REP

thank you!

[JeteMc]

You're Welcome, thank you for the feedback and marking the thread as solved. I hope that you have a blessed day.