Index/Match or VLOOKUP

[tgold77]

I'm trying to create a formula that will search a 2 column array and return multiple results into one cell. Or I could move the formula along the row to return one result into each cell horizontally, and then aggregate them.

The problems I'm having are as follows:

1. The formula only finds the top result and then stops.
2. I'm using the $ symbols to try and anchor the array but it moves as a paste the formula down the column.
3. Most of the names I'm searching for are in a cell alone, but some of them are joint info. So lets say I'm searching for "A" and I know there are 3 results (A=1, A=2, A/B=3). Is it possible to get a forumla that will return the info contained in a cell or only an exact match?

Please help!

Here is what I've been trying if it helps.

=VLOOKUP(D2,$A2:$B428,2,FALSE)
=INDEX($A2:$B428,MATCH(D2,$B2:$B428,0),1)

[Fotis1991]

Looks like to need an Array INDEX&MATCH.

cAN YOU UPLOAD A SMALL SAMPLE WORKBOOK?

[tgold77]

Here is an example with columns that should match to my example formula's. Thanks for your help.

[tgold77]

I've figured out #2 but I could still really use help with 1 and 3. Thanks!

[Fotis1991]

Unfortunately, i am not able to follow you.

Can you type, manually some expected results explaining why these are correct,please?

[tgold77]

Certainly. In my example Spread Sheet we have columns A-G and I would like the formula to be in G. The other columns match my actual Spread Sheet which is thousands of lines long. This issue has to do with moving accounts to another firm, and the reps are all getting new rep codes. So I need to aggregate all the new rep codes into a single cell adjacent to the reps name, and I would like to be able to show the formula in case there is an issue in the future. This is further complicated by Joint Rep Codes that prevent an exact match for each name.

The "First" and "Last" Columns are useless here but I've left them in to match. I want the formula in G to search the name in the same row of D and then find all instances of that name in column A. Then I would like the adjacent info in Column B placed into G.

In my example, in the D column, "A" is in cell D25. I would expect the return in G25 to be 1,15,16,17,20. This is the A individual result (B1) and the 4 joint results (B16:B20). I believe there may be a wildcard that will allow me to identify when my search result is contained in a large text string in a cell. But I've also been able to get a total return. I only get the 1st result and then it stops.

=INDEX($A$2:$B$428,MATCH(D2,$B$2:$B$428,0),1)

Thanks again.

[Haseeb Avarakkan]

If you want to return multiple results in a single cell, will require VBA.

If it is OK to return results in multiple columns...

In G2, with CTRL+SHIFT+ENTER not just ENTER

=IFERROR(INDEX($B:$B,SMALL(IF(ISNUMBER(SEARCH($D2,$A$2:$A$100)),ROW($A$2:$A$100)),COLUMNS($G2:G2))),"")

Then copy down & across.

[tgold77]

It's not working for me. I pasted it in and it gave me the corresponding data in B2 and I type it in and held down CTRL+SHIFT+ENTER when I was finished and the cell was blank. When do I do the CTRL+SHIFT+ENTER? Just to finalize the formula? Thanks!

[Haseeb Avarakkan]

See the attached,

Also contains VBA with ACONCAT function, which will return results in a single cell.

[tgold77]

Thank you so much, but it's still not working for me. I'm getting the #NAME? error. I may not be enabled for VBA. I'm not even sure what that is. I'm Ok posting the results accross multiple columns but that isn't working either. I don't understand the CTRL+SHIFT+ENTER thing.

[Haseeb Avarakkan]

This is the code used for BA nohing else

Please Login or Register  to view this content.

CTRL+SHIFT+ENTER is the key to activate an Array Formula,

Please see the below link more about Array Formulas.

http://www.cpearson.com/excel/ArrayFormulas.aspx

[tgold77]

Thank you for your help. I think some of my data is bad, so I'm going to go through and clean it up and try again. I'm really impressed with how quickly you came up with such complex formula's. When I grow up I'm going to be an Excel Guru like you! Thanks again.

[tgold77]

Hi again. I've rebuilt everything but I'm still having trouble. I think it's an issue with the array. I've read some of the article you linked but I haven't been able to figue it all out yet. When I paste or type in your formulas into my workbook it doesn't work at all. But when I copied the example you uploaded into a new tab in my workbook and pasted in my real data, it worked like a charm. Up until line 25 that is. When I tried to expand the formula by copying and pasting, it doesn't work out side of the range you've created.

So my question is, how do get the formula into the range you want? The article you linked suggested:

1. Highlight the range you want.
2. Type in the formula you want.
3. Press CTRL+SHIFT+ENTER to activate the array (not sure what that means)

That's not working for me, but in the 1st formula you suggested:

In G2, with CTRL+SHIFT+ENTER not just ENTER

=IFERROR(INDEX($B:$B,SMALL(IF(ISNUMBER(SEARCH($D2,$A$2:$A$100)),ROW($A$2:$A$100)),COLUMNS($G2:G2))),"")

Then copy down & across.

Does this mean:

1. Select G2
2. Type in the formula
3. hit CTRL+SHIFT+ENTER
4. Copy G2
5. Select desired range and paste.

Sorry to be so dense but I've tried everything I can think of and it's just not working. Also, what is the $A$2:$A$100 you use? Why 100? That's not the number of rows that I have, but when I put in the exact number of rows it only makes it worse.

Thank you!

[Haseeb Avarakkan]

...
Does this mean:

1. Select G2
2. Type in the formula
3. hit CTRL+SHIFT+ENTER
4. Copy G2
5. Select desired range and paste

Yes. exactly.

...what is the $A$2:$A$100 you use? Why 100? That's not the number of rows that I have, but when I put in the exact number of rows it only makes it worse.

I just used used A2:A100 as sample. You can adjust it to your data range. DO NOT use whole column reference like A:A

[tgold77]

Thanks again.