Need to Combine Index, Small(If with an Or

[amy22x3]

I have a coworker that maintains a spreadsheet comprised of several tabs, the first is the main and contains all the information about each business partner with columns showing their 1st choice wholesaler, 2nd choice, 3rd choice and so on. And then there's a tab for each of the wholesalers listed on the main page. I've been asked to automate each of the wholesaler tabs to automatically populate with the partner name and contact information regardless of what order that wholesaler was chosen. The problem with index(match is that it picks up the first instance of a partner and repeats that name each time that partner shows up with a different wholesaler name. So I did sort that out with this formula and it works perfectly....for one column of wholesaler.

=IFERROR(INDEX($B$6:$B$8,SMALL(IF($G$6:$G$8=B17,ROW($G$6:$G$8)-ROW(INDEX($G$6:$G$8,1,1))+1),A19)),"")

But I have 6 columns of wholesalers and I can't figure out how to make this work for each instance of partner listed vertically and then make it work for each wholesaler listed horizontally. I've attached an example spreadsheet, but can't figure out how to expand this from just Column G. Ok I tried to attach spreadsheet, but the icon isn't working. I've attached a pic.

Attachment 507607

Any help is greatly appreciated!

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[amy22x3]

Ok, got the attachments figured out. Thanks.

[canapone]

Hi,

in B19 array entered (confirmed with control+shift+enter)

=IFERROR(INDEX(B$6:B$8,SMALL(IF(G$6:K$8=LOOKUP(2,1/(B$13:B17="This is tab name below:"),B$14:B18),ROW($6:$8)-ROW(A$6)+1),A19)),"")

to be copied down to B20-B21-B26...

If you are an Excel 2010 user, we could exploit AGGREGATE too.


Regards

[amy22x3]

I'm confused by the "B$13:B17="This is tab name below". The AAA is what I need to reference, so I can fix that, but there's nothing in B13-B17 of relevance, so I'm not following.

[canapone]

Hi,

I've used LOOKUP(2 ...with a dynamic range in order to adjust automatically related reference (AAA...FFF)

If it's confusing, in B19 to be confirmed with control+shift+enter

=IFERROR(INDEX($B$6:$B$8,SMALL(IF($G$6:$K$8=B$17,ROW($6:$8)-ROW(A$6)+1),A19)),"")

When you copy the formula in B26, please adjust cell reference: B$24

Same outputs.

Hope it helps

[Glenn Kennedy]

This can be considerably simplified to this array formula:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$F$6:$J$8=$A$2,ROW(Sheet1!$F$6:$F$8)),ROWS(A$4:A4))),"")

I have created one of the other tabs, rather than cluttering up your source sheet with the answers.

[Glenn Kennedy]

Canapone... i didn't see your reply above. You got there while I was away tending to 25 litres of Home Brewed ale!!

[amy22x3]

This worked perfectly!!!! Thank you so much, wow I worked on this for a couple hours yesterday and just couldn't get it. I am confused about referencing the cell that I'm typing the formula into though. I didn't know you could do that, I always thought that would be a circular error. Can you explain how this worked?

Thanks, Canapone for your help as well!

I'll mark this solved.

[Glenn Kennedy]

ROWS(A$4:A4) is simply a counter... It removes the need to have that additional column of yours: 1, 2, 3, etc.

in A4, it resolves to 1, in A5, to 2, etc. I looked at canapone's first solution and didn't understand it. So, while to-ing and fro-ing to my garage, I fiddled with it so that it was understandable. We both got to more-or-less the same end point... though he was 30 mins quicker than me.