Index Match

[j4ymf]

Hello

I was wondering how would i change this formula
Currently it looks to match the Name in B8 to the ones in F:F and return the value in row 3.

I would like it to return if there are more that one answer to return the answer,

currently it returns my answer as 7718 - Student Castle Cambridge if i have 2 different answers can it return it like ? 7718 - Student Castle Cambridge - 7289 - Student Castle London

=IFNA(INDEX(Download!$B$1:$Q$9999,MATCH(B8,Download!$F$1:$F$9999,0),3),"")

many thanks Jason

 

[j4ymf]

=IFNA(INDEX(Download!$B$1:$Q$9999,MATCH(B8,Download!$F$1:$F$9999,0),3),"")

or how do i use this code to match the second answer in the list

thank you

 

[AhoyNC]

What version of Excel are you using? If it's Excel 365 do you have the FILTER function?

 

[j4ymf]

Hello AhoyNC

Not sure what version im using but after asking my first question i now realise that i would like to create a sheet that looks like this
the first result works OK and returns 7718 - Student Castle Cambridge

how do i change the formula so i can use it in the Site 2 cell

Does that make sense

So i need a formula that matches the first answer in the list and the second answer

Thank you

 

[AhoyNC]

You can try something like this which will work with Excel ver. 2010 or later. You can find your version by going to FILE - Account.

Book1
	A	B	C	D	E	F	G
7
8		Joe Bloggs	7718 - Student Castle Cambridge			Joe Bloggs
9			Test1			Sam Jones
10			Test2			Judy May
11			7289 - Student Castle London			Joe Bloggs
12			Test4			Paul Jones
13
14
15		Total	JN- Site 1	LW	SR	% Wage	JN- Site 2
16		Joe Bloggs	7718 - Student Castle Cambridge				7289 - Student Castle London
Sheet1

Cell Formulas
Range	Formula
C16	C16	=IFERROR(INDEX($C$8:$C$12,AGGREGATE(15,6,(ROW($C$8:$C$12)-ROW($C$8)+1)/($F$8:$F$12=$B16),1)),"")
G16	G16	=IFERROR(INDEX($C$8:$C$12,AGGREGATE(15,6,(ROW($C$8:$C$12)-ROW($C$8)+1)/($F$8:$F$12=$B16),2)),"")

 

[j4ymf]

Thank you AhoyNC
your a star


Im trying to understand the code, what does the AGGREGATE(15,6,

15,6 mean?

 

[j4ymf]

hello AhoyNC

thank you

=IFERROR(INDEX(Download!$D$1:$D$9999,AGGREGATE(15,6,(ROW(Download!$D$1:$D$9999)-ROW(Download!$D$1)+1)/(Download!$F$1:$F$9999=B22),35)),"")

I have had a play and changed it to my RANGE and i needed to change the last bit to get the correct answer to 35

if i have a long list im looking for the next different name to the first, not always the second in the list

thank you in advance

 

[AhoyNC]

The 15 is the option for aggregate to use the SMALL function. The 6 tells it to ignore error values.
IFERROR(INDEX($C$8:$C$12,AGGREGATE(15,6,(ROW($C$8:$C$12)-ROW($C$8)+1)/($F$8:$F$12=$B16),1)) -the 1 tells the function to find the first row with the data that matchs your look up, the 2 in the second formula tells it to find the 2 value that matches your data. You will need to change the ranges to match your data and sheets. The ranges need to start with the first cell that actual holds your data.

 

[j4ymf]

Thank you AhoyNC

Sorry, i may have asked the question wrong, how do i find the next new name, i asked for the 2nd in the list
I meant the 2nd or different name

thank you

 

[AhoyNC]

One way would be to list the names in your summary sheet. You could copy the names from your data sheet and then use the remove duplicates under the data ribbon on your copied data.
Something like below.

Book1
	A	B	C	D	E	F	G
7
8		Joe Bloggs	7718 - Student Castle Cambridge			Joe Bloggs
9			Test1			Sam Jones
10			Test2			Judy May
11			7289 - Student Castle London			Joe Bloggs
12			Test4			Paul Jones
13			Test5			Judy May
14
15		Total	JN- Site 1	LW	SR	% Wage	JN- Site 2
16		Joe Bloggs	7718 - Student Castle Cambridge				7289 - Student Castle London
17		Sam Jones	Test1
18		Judy May	Test2				Test5
19		Paul Jones	Test4
Sheet1

Cell Formulas
Range	Formula
C16:C19	C16	=IFERROR(INDEX($C$8:$C$13,AGGREGATE(15,6,(ROW($C$8:$C$13)-ROW($C$8)+1)/($F$8:$F$13=$B16),1)),"")
G16:G19	G16	=IFERROR(INDEX($C$8:$C$13,AGGREGATE(15,6,(ROW($C$8:$C$13)-ROW($C$8)+1)/($F$8:$F$13=$B16),2)),"")