INDEX, SMALL, COUNT, MATCH, I Dont Know.....

[DocBlack4444]

Hello Community,

I am trying to figure out how to get Excel to extract a row from one sheet and post it to another based on criteria that (contains) the matching list from a third tab.

I know some formulas, but i am really a novice at this. I tried to research and work my own formula and came across a very interesting example but it is not working.

the Formula that i am working with, or at least trying to is this:

Please Login or Register  to view this content.

Main tab is 'ALL', Tab that contains the criteria is 'Clinic List' and i want to extract to 'MHC' the same headers exist on all tabs as ROW 1.

I had the idea to have excel help me to filter down to different Sheets based on criteria, but the formula is driving me up a wall.

[Limor_OP]

None of the lines in column E "ALL" tab match column L in "CLINIC LIST" tab....

[DocBlack4444]

None of the lines in column E "ALL" tab match column L in "CLINIC LIST" tab....

We have a program that spits out a sequence number PRIOR to the actual text that is listed in Column L in the "Clinic List". i was hopeful that i could do like a "*"& before and &"*" after to allow for the variance. i guess i forgot to add that.

[Limor_OP]

Hi,

How about adding another columnin in "ALL" sheet to lose the sequence number PRIOR to the actual text with the following:
=MID(E2,14,100)

Then in "MCH" tab I listed all the instances where the text in "CLINIC" tab (column L) is found in "ALL" tab
If it is found more than once - then the text will repeat itself the number of intances found.

This is the formula in column A ,"MCH" tab:
=IFERROR(INDEX('Clinic List'!$L$1:$L$1800,AGGREGATE(15,6,MATCH(ALL!$H$2:$H$200,'Clinic List'!L:L,0),ROWS($F$1:F1))),"")

Column B is extracting the names with below formula:
=INDEX(ALL!$A$1:$A$200,AGGREGATE(15,6,(ROW(ALL!$A$1:$A$200))/(MHC!A2=ALL!$H$1:$H$200),COUNTIF(MHC!$A$2:A2,MHC!A2)))

The rest - column C and forwards is a simple vlookup, you can drag it down and across:
=VLOOKUP($B2,ALL!$A:$G,COLUMN(B1),0)


Hope this is what you are looking for, I'm sure it can simplified but that's what I came up with.

[DocBlack4444]

Hi,

How about adding another columnin in "ALL" sheet to lose the sequence number PRIOR to the actual text with the following:
=MID(E2,14,100)

Then in "MCH" tab I listed all the instances where the text in "CLINIC" tab (column L) is found in "ALL" tab
If it is found more than once - then the text will repeat itself the number of intances found.

This is the formula in column A ,"MCH" tab:
=IFERROR(INDEX('Clinic List'!$L$1:$L$1800,AGGREGATE(15,6,MATCH(ALL!$H$2:$H$200,'Clinic List'!L:L,0),ROWS($F$1:F1))),"")

Column B is extracting the names with below formula:
=INDEX(ALL!$A$1:$A$200,AGGREGATE(15,6,(ROW(ALL!$A$1:$A$200))/(MHC!A2=ALL!$H$1:$H$200),COUNTIF(MHC!$A$2:A2,MHC!A2)))

The rest - column C and forwards is a simple vlookup, you can drag it down and across:
=VLOOKUP($B2,ALL!$A:$G,COLUMN(B1),0)


Hope this is what you are looking for, I'm sure it can simplified but that's what I came up with.

THIS IS AWESOME! so much more complicated then what I was thinking. Thank you for making sense of this. I can duplicate on all the other tabs to make it match each section as well. I really appreciate you doing all this work for me.

I just put live data into the "ALL" tab and it came up with a different list on the "MHC" tab in column A then the rest of the columns. I will simply hide column A and it works perfect and i can see exactly what I need to see.

[Limor_OP]

Hello DocBlack4444 ,
Glad this helps and works for you. It did took me a while to solve this, it was not a kind of a straight forward answer to figure that out, so thanks for your feedback : )