How to find a 2nd match value in Excel

[ChelseaScooby]

Hi, MrExcel virgin here....

I have two formulas that find the data for a 1st hit, (either =VLOOKUP(A107,'Yr6 Wk1'!C5:D250,COLUMN(D:D)-COLUMN('Yr6 Wk1'!C5:D250)+1,0) OR =IF(ISNA(MATCH(A107,'Yr6 Wk1'!$C$5:$C$251,0)),"",VLOOKUP(A107,'Yr6 Wk1'!$C$5:$D$251,2,FALSE)). These both work fine, what I now need is to search if there is any additional matches, (Most of the time there will not be but I need to allow for the possibility) I can either combine all hits in the same column or add additional hits into new column it doesn't matter. probably a small tweak but can't see it atm...

Thanks in advance

 

[Scott T]

You may find this video on returning multiple items helpful

https://youtu.be/SCiqQiC7Bwk

 

[ChelseaScooby]

Thanks for reply, unfortunately as good as the video is it's no good for this situation, . Any other offers? is there any way I can use the original If(ISNA formula to use the 2nd 'hit?

 

[AlanY]

here is an example to find 2nd, 3rd match etc, any good?

Excel 2013/2016
	A	B	C	D	E
1
2	Philip	2.59%		Philip	2.59%
3	Philip	2.43%		Philip	2.43%
4	Jone	5.00%		Philip	3.00%
5	Rose	3.25%
6	Philip	3.00%
7
2

Cell Formulas
Range	Formula
E2	=INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=D2,ROW($A$2:$A$6)-(ROW($A$2)-ROW($A$1))),COUNTIF(D$2:D2,D2)))

 

[ChelseaScooby]

Hi Alan, it's not far off but not quite, I don't think it matters but what I am matching is over 2 pages, from what I can work out B= Page 1 Column D (the info I want to extract & use) based on search results from, A (Page 1 Column C), which is searching for matches with Column D (Page 2 column A) & I need possible multiple answers (Max 3), Column E on your sheet to go in Columns BI, BJ & BK on page 2, I'll then be repeating the process in each row hence need to place answers alongside & not below.

Thank you, i'm sure it's no far off.