INDEX/MATCH Problem!

[Aitch]

=INDEX(A2:AA6 , MATCH(MINIFS(B2:B6 , A2:A6 ,"??*") , B2:B6 , 0)
	1
	4
Jack	1
	2
Jill	3

Hoping someone can fix this one!

A1 is the INDEX/MATCH - which should show the name of the person in column A which has the lowest score in column B - in this case Jack with 1

However, there are a lot of missing names in column A - so I used a MINIFS to filter out the blank cells...

Unfortunately it looks up the first instance of 1 in B2 which results in the blank cell in A2....

Is there a better way to do this?

Basically I need to find the MIN of column B which also has text in column A - and then show the text right next to the MIN in column A

Appreciate any help!

 

[CA_Punit]

Since my Excel2bb aadin is working

try with Ctrl+Shift+Enter

 

[jtakw]

Hi,

Your profiles says you have Excel 2010, which doesn't have the MINIF(S) function(s), anyway, try it this way:

Book3.xlsx
	A	B
1	Jack
2		1
3		4
4	Jack	1
5		2
6	Jill	3
Sheet898

Cell Formulas
Range	Formula
A1	A1	=INDEX(A2:A6,MATCH(MIN(IF(A2:A6<>"",B2:B6)),(A2:A6<>"")*B2:B6,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aitch]

Less than 5 minutes lol - you guys rock!

Is there any way to make it work without an array formula ??

 

[Fluff]

Are you actually using 2010? As jtakw said 2010 doesn't have minifs.

 

[Toadstool]

Hi Aitch,

My solution

Aitch2.xlsx
	A	B	C	D	E
1
2		1			Jack
3		4
4	Jack	1
5		2
6	Jill	3
Sheet1

Cell Formulas
Range	Formula
E2	E2	=INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($A$2:$A$6)-ROW($A$1)/(($B$2:$B$6=AGGREGATE(15,6,$B$2:$B$6/($A$2:$A$6<>""),1)*($A$2:$A$6<>""))),1))

 

[Fluff]

Another option

=INDEX(A2:A6,AGGREGATE(15,6,(ROW(A2:A6)-ROW(A2)+1)/(A2:A6<>"")/(B2:B6=MIN(B2:B6)),1))

 

[Toadstool]

Another option

=INDEX(A2:A6,AGGREGATE(15,6,(ROW(A2:A6)-ROW(A2)+1)/(A2:A6<>"")/(B2:B6=MIN(B2:B6)),1))

I don't believe that works if a row without a name has the lowest value in the column. e.g. change the first 1 to 0.

 

[Fluff]

Good point. Lets wait & see what the OPs response is to post#5

 

[Aitch]

Are you actually using 2010? As jtakw said 2010 doesn't have minifs.

I'm trying out the free version of Office 365.... would you recommend the upgrade Fluff?

Kinda hesitant as it's never ending payment lol and 2010 does 99% of what I need


Many thanks guys, will try these out!