Index match returning 0s or duplicate rows

[awelearning]

Hi - I've been struggling with this for a while and can't figure out what I'm doing wrong. (I also can't figure out how to embed images here and I've reached *maximum stress*, so I hope it's ok that I linked to G drive to show you my screenshots)

I want to pull only rows from one worksheet that have EHCP in column G, to another worksheet. I've tried index match in a number of ways, but one brings over N/A rows as 0, and the other repeats rows.

Source worksheet:

https://drive.google.com/open?id=1YRwfw4KOq1UK_H9CM2E9LsVBzw5QO052

Formula returning 0s (used to be N/A but I added the ISNA function): =IF(ISNA(MATCH("EHCP",'Support Planning'!$G2,0)),,INDEX('Support Planning'!A2:A9999,MATCH("EHCP",'Support Planning'!$G2,0)))
Worksheet https://drive.google.com/open?id=1gPaTPsupTRswDWhsfqhMXu3uyG7z0QAd

Formula returning duplicates:=INDEX('Support Planning'!A2:A15,MATCH('Look Ups'!$A$6,'Support Planning'!$G2:$G15,0))
Worksheet https://drive.google.com/open?id=1zAfgq3eKZy3hflLh4f85arYtYsyA_ClR

Why am I getting these unnecessary rows and how do I get rid of them? Thanks in advance for any advice and guidance

 

[Special-K99]

Images dont allow us to examine the data.

Post the spreadsheet to an online storage site (as youve done with the image) and post the link here.

 

[awelearning]

Link to spreadsheet: https://drive.google.com/open?id=1zp9-g5RO5VoDuqeL88x7WhucsHne2Jwx

Thanks

 

[MARZIOTULLIO]

Try

HCP

A2=IFERROR(INDEX('Support Planning'!A$2:A$16,SMALL(IF('Support Planning'!$G$2:$G$17='Support Planning'!$G$2,ROW('Support Planning'!A$2:A$16)-ROW('Support
Planning'!A$2)+1),ROWS('Support Planning'!A$2:'Support Planning'!A2))),"") control +shift+enter copy down and across

 

[awelearning]

It worked perfectly! Thank you! I've seen this "SMALL" thing in recommendations but not seen a decent tutorial. Can you point me to an idiot guide for what is happening here, please?

 

[MARZIOTULLIO]

You are welcome
<yt-formatted-string class="style-scope ytd-video-primary-info-renderer" style="--yt-endpoint-color:hsl(206.1, 79.3%, 52.7%);">Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples)</yt-formatted-string>


https://www.youtube.com/watch?v=bMWv0AN-hSs


https://www.youtube.com/user/ExcelIsFun best channell for learn Excel

 

[awelearning]

Thank you, I'll go and educate myself