HOW TO SOLVE RETURNING THE FIRST VALUE ONLY (INDEX MATCH)

[AYSHANA]

Good Day!

i would like to ask for a help. why my Index Match formula is returning the first value only.
if anyone can tell me what mistake im doing.

thank you.

Book1
	A	B	C
1	ACCC	UNIT NUMBER Allocated in BGRPS order	BLOOD BGRP
2	M321925	0
3	W488807	0
4	X338924	0
5	T771439	0
6	F391568	0
7	T791935	0
8	W476272	U240123006498
9	W476272	U240123006498	it should be U240123006507
10	T787716	U240123002474
Sheet2

Cell Formulas
Range	Formula
B2:B10	B2	=INDEX(Sheet1!$D$2:$G$10,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$10,0),1)

Book1
	A	B	C	D
1	Acc No#	PT Number	PT Name	UNIT NUMBER Allocated in BGRPS order
2	M321925
3	W488807
4	X338924
5	T771439
6	F391568
7	T791935
8	W476272			U240123006498
9	W476272			U240123006507
10	T787716			U240123002474
Sheet1

 

[Legacy 143009]

You'll need this:

=INDEX(Sheet1!$D$2:$D$10,SMALL(IF((Sheet2!$A2=Sheet1!$A$2:$A$10)*1,ROW($A$1:$A$9)),ROWS($A$1:A1)))

This is an array formula. You must press Ctrl+Shift+Enter together after paste.

 

[Fluff]

A normally entered formula

=INDEX(Sheet1!$D:$D,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$10)/($A2=Sheet1!$A$2:$A$10),COUNTIFS(A$2:A2,A2)))

 

[Legacy 143009]

This should work better:

=INDEX(Sheet1!$D$2:$D$10,SMALL(IF((A2=Sheet1!$A$2:$A$10)*1,ROW($A$1:$A$9)),COUNTIF($A$1:A2,A2)))

This is an array formula also. You must press Ctrl+Shift+Enter together after paste.

 

[Legacy 143009]

A normally entered formula

=INDEX(Sheet1!$D:$D,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$10)/($A2=Sheet1!$A$2:$A$10),COUNTIFS(A$2:A2,A2)))

Hi Fluff,

Is there a particular reason that you've used COUNTIFS instead of COUNTIF?

 

[Fluff]

Is there a particular reason that you've used COUNTIFS instead of COUNTIF?

Nope, just habit

 

[AYSHANA]

Hi Fluff,

Is there a particular reason that you've used COUNTIFS instead of COUNTIF?

thank you

 

[AYSHANA]

A normally entered formula

=INDEX(Sheet1!$D:$D,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$10)/($A2=Sheet1!$A$2:$A$10),COUNTIFS(A$2:A2,A2)))

thank you

 

[Fluff]

Glad we could help & thanks for the feedback.