Multiple Match/Index for Individual Tabs

[hartjohnson]

Hey everyone!

Having a major brain fart.

First off, here is the link to the workbook I am working with: This is a Google Sheet (I know, I want it in Excel but several people that will need access to this do not have Excel) https://docs.google.com/spreadsheets...it?usp=sharing

What I am trying to accomplish is for the data to pull from the 'Business by Rep' sheet to the corresponding Rep sheet. For example, Test 1 is Hart's business so it should only display on his tab.

I hope this makes sense, I think a nested If should work, but not really getting it to display multiple results like I want.

I do have a formula that works and shows the first result, but I can't get it to show multiple results properly

Thanks in advance everyone!

[JohnTopley]

Please post a file to the forum as many members do not wish to visit file-hosting sites.

[hartjohnson]

Please post a file to the forum as many members do not wish to visit file-hosting sites.

Hi John:

It isnt a file hosting site. This is housed in Google Sheets (I have to do it there for multiple people to have access that don't have excel).

[hartjohnson]

Hi guys! I uploaded a sample data set in Excel format if it makes it easier. Thanks! Still stuck

[Pete_UK]

You can put this in D2 of the Rep_Sheet:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

Copy down as far as you wish to (the hyphens will show where the formula is active, to help you cope with new data being added).

Then you can put this formula in A3 of the subsidiary sheets:

=IFERROR(INDEX('Rep Sheet'!A:A,MATCH($A$1&"_"&ROWS($1:1),'Rep Sheet'!$D:$D,0)),"")

Copy across and down, as required.

Note: you will need to change the name in cell A1 of the Hart sheet.

Hope this helps.

Pete

[hartjohnson]

You can put this in D2 of the Rep_Sheet:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

Copy down as far as you wish to (the hyphens will show where the formula is active, to help you cope with new data being added).

Then you can put this formula in A3 of the subsidiary sheets:

=IFERROR(INDEX('Rep Sheet'!A:A,MATCH($A$1&"_"&ROWS($1:1),'Rep Sheet'!$D:$D,0)),"")

Copy across and down, as required.

Note: you will need to change the name in cell A1 of the Hart sheet.

Hope this helps.

Pete

What do I need to change the name to in A1? Im a bit confused.

[Pete_UK]

It should be Hart. In the file you attached it was Ben (copied from the Ben sheet).

Hope this helps.

Pete

[hartjohnson]

It should be Hart. In the file you attached it was Ben (copied from the Ben sheet).

Hope this helps.

Pete

So it is working, but wont pull the first result for either person. It starts with whatever the second data set is for that person.

[Sinon05]

You can try other formulae than the one I used by searching on google for "excel lookup multiple results".

The one I used is:

http://www.get-digital-help.com/2009...okup-in-excel/

When you apply the formula in your workbook update the ranges. Not sure how well this will work in google sheets though.

[hartjohnson]

That works!!! Thanks!!!!!!!!!!!!! YIPPEE!!!!

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete