Match multiple names showing row numbers

I am very limited n my knowledge with excel and could use some help for my business....
I have monthly entry's, each sheet named by month.... on a separate sheet, lets call it DATA, I have the whole year indexed for fast lookup.
I want to be able to find multiple MATCH's shown for each month showing its row number, separated by a comma, and showing all the multiple instances of a certain name, and all its row numbers in a single cell.
I was able to figure out how to find 1 instance of a name with this...
example: =MATCH(A1,JANUARY!$B$1:$B$1000,0)

this example returns the row number where to find the name I am looking for, which is great...
but if there's multiple entry's of this same name in the month, then I need it to show that same name and have it list all its row numbers.

for example if NAME1 is on row 191, then it will show: 191
this is good... but, I would like it to show more matches of the same name and its row number... in a SINGLE CELL.

for example : 191, 237, 278

This will help me quickly find NAME1 and its exact row number on the monthly sheet, and all the re-accruing instances of that same name.

I tried this: =MATCH(A1,JANUARY!$B$1:$B$1000,0)&","&MATCH(A1,JANUARY!$B$1:$B$100,0)
but it just shows the same NAME1 repeated... : 191,191

I should mention, on the DATA sheet, the 1st row has all the names list in column A,
columns B through M are the month's, which would contain the formula.

And to make it correctly show its proper row number if there's a header on the MONTHLY sheets... I had to add how many rows the header was... so,
if 2 top rows are used as a header, the list actually starts on row 3,
so I used this instead to show the proper location... : =MATCH(A1,JANUARY!$B$1:$B$1000,0)+2
this would correctly show its location for that month.

I greatly appreciate any suggestion or help with this.

I'll try a small example here... this will show in the cell: 3
I would like it to show: 3, 5, 6, 8


( January (tab) )

A B
Date Client Amount

1/1/2018 NAME1 $100
1/1/2018 NAME2 $75
1/1/2018 NAME1 $65
1/2/2018 NAME1 $120
1/2/2018 NAME2 $200
1/2/2018 NAME1 $140
1/2/2018 NAME3 $120


( DATA (tab) )

Clients JAN

NAME1 =MATCH(A3,January!$B$3:$B$600,0)+2
NAME2
NAME3
NAME4
NAME5
NAME6
NAME7
NAME8
NAME9
NAME10

Attach a sample workbook (not a picture or text). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Ok, I will give it a go, and do my best...
I did a mock up with just the month of MAY in this excel book, I didn't include all other 11 months because you don't need them to see the example...
So as you can see in this excel sheet on the DATA tab sheet, cell G2 shows "3", I would like it to show all the instances of "NAME1" from the "MAY" tab sheet , so cell G2 should show....
3,5,8
because NAME1 is on the month of MAY and in row 3, row 5 and row 8.
cell G3 shows 4,
I would like it to show 4,10.
Because NAME2 is on the month of MAY and in row 4 and row 10.
and so on and so forth...

If you look at cell G7 on the DATA tab sheet, I put the example I tried that doesn't work... it just shows 7,7
it should show ...
7,11

Maybe a whole new function is needed to give proper results...
any help with this is greatly appreciated!

Frank..

is that what you want?
done with PowerQuery

Originally Posted by sandy666

is that what you want?
done with PowerQuery

Hi, thanks so much for the reply...
I would like the results on the DATA tab sheet, in the corresponding cells, just as I have it now.
but yes, your example that you show on the month sheet is correct, but I don't want this to display the results on the month sheet, I would like the row numbers to display right where they are under each column on the DATA sheet....
The #NA is important to me also, because it shows under which month that this client isn't present, which is what I like.

I don't know if the MATCH formula will work for this, ... maybe VLOOKUP? not sure because I'm not skilled enough.

thanks again.
Frank...

it doesn't matter where result table is it was easier to me put it on source tab.
for the rest.... I can do that for whole data not a part - you gave a part of data only so you got part answer
in your example NAME4 doesn't exist so it doesn't exist in result table so I can't use NA for this

the reason for having this displayed on the separate sheet, "DATA" sheet, so that I can open that sheet and have a quick overview of the whole year, and see a client name, under the corresponding month, and where to find them on any given month, with all occurrence's, as there is 12 sheets/tab's , with each month displayed separately. Jan, Feb, Mar... ect..

so, is it possible to have the result table on the DATA sheet? for each month as I have it now? with the proper syntax/formula.. what ever that may be

Frank

I have no idea how you got the numbers to show under the month....
and all the MATCH syntax's are gone.. so still no idea what's happening here... or how you got the number's to show.

when I am entering data on the current month... like MAY, it should automatically populate under the proper month column on the DATA sheet.

I tried entering new dates/names on your example sheet... but nothing shows on the DATA sheet.

If I need power query software, to do this.. then this is a whole new learning curve for me.. and its getting way to complicated to do such a simple thing.

There's got to be some other formula to do this task within excel as it is.

Frank

Re-read post#8
if this is so simple with formula - wait for formula solution, no problem

@Dave,
You forgot about #N/A in G5

@Dave
Ha ha ha, I think you are too perfect exceler to damage something here. Just too fast person, I suppose

Very nice you guy's !! absolute genius's.. and very much appreciated.
It's working as expected!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Gotchya !