Excel 2007 Possibly VLookup with Match, Index, Row???

[BlueHorizon]

Hi all,

I have a workbook with two worksheets.
Worksheet1 shows a number in Col A, i.e., 1, 2, 3, 4
Worksheet1 shows a text name in Col B, i.e., Washington, Lincoln,

It can look like this:
Col A Col B
1 Washington
2 Lincoln
3 Grant
3 Washington
2 Grant
1 Grant
1 Lincoln
3 Lincoln

On a separate worksheet, I want to create a formula that will look up each occasion of a number in Col A and return the text that is in Col B. If a number appears 4 times in Col A, I need all 4 entries in Col B to be returned (in separate cells).

In this example, the number 1 in Col A would return (in separate cells)
Washington
Grant
Lincoln

Any help would be appreciated - it may be a combination of formulas I know but don't know how to put them together.

TIA,


Best,
Blue Horizon

 

[SkipVought]

hi,

What is the relationship between the NUMBER in column A and the namees in column B?

I would use MS Query, via Data > Get external data to return the data. You can make it a parameter query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BlueHorizon]

Thanks anyway, Skip - I found the answer using an array formula.

Kathy

Best,
Blue Horizon

 

[SkipVought]

Please post your solution for the benefit of other membes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BlueHorizon]

I didn't post it because it may not make sense to other users, but here it is:

{=INDEX('2011 Schedule'!A:B,SMALL(IF('2011 Schedule'!A:A=List!$E$32,ROW('2011 Schedule'!$A:$A)),ROW(1:1)),2)}

List!$E$32 = this represents the 1st number in the list, i.e., "1". When I use the Autofill to populate other cells, the last ROW reference changes to 2:2, then 3:3 etc.

When I need to have it look for the next number in the series, i.e., "2", I copy/paste the formula into a new cell, change the List!$E$32 reference to List!$E$33 where I've entered the next number in the series, and make sure I change the last ROW reference over again as 1:1. IMPORTANT: Use Ctrl + Shift + Enter to enter the formula each new time you use it.

Hope this helps someone....
Thanks!

Best,
Blue Horizon

 

[lionelhill]

This is also exactly what pivot tables do. If you create a pivot table from the data in worksheet 1, using column A as your category column dragged to the left of the pivot table, then your table will have rows 1, 2, 3.... and so on. Clicking on "2" will create a new worksheet containing all the pivot table entries that have "2" in the first column.

 

[BlueHorizon]

Thanks for the suggestion. We did it using this formula so that we could have the references in a single column on a specific worksheet. That way we could reference that data from another worksheet that was already using a VLookup. Kathy

Best,
Blue Horizon