Index and match to identify lowest answer from a range

[Sprinter99]

Hi ,
I have a requirement to lookup open cases that a user may have with the helpdesk, some users have multiple cases over a period of time and that could be 1,2,3 or 4 (Priority 1 being the highest importance and priority 4 being the lowest priority).

I am trying to user index and match against a username which is unique on both reports to identify the highest priority (or lowest number) for that user and ignore all other cases that they may have opened.

I am no longer sure that Index and Match is the way to go, so any assistance would be very much appreciated.

Question.xlsx
	A	B	C	D
1	username	Lowest priority case		Notes
2	User_1	2		the answer I am looking for is "1" as that is the lowest
3	User_2	0
4	User_3	2
5	User_4	0
6	User_5	0
7	User_6	0
8	User_7	4		the answer I am looking for is "3" as that is the lowest
9	User_8	0
10	User_9	0
11	User_10	0
12	User_11	0
13	User_12	0
14	User_13	0
15	User_14	0
16	User_15	0
17	User_16	0
18	User_17	0
19	User_18	0
20	User_19	0
21	User_20	0
22	User_21	0
23	User_22	0
24	User_23	0
25	User_24	0
26	User_25	0
27	User_26	0
28	User_27	0
29	User_28	0
30	User_29	0
31	User_30	0
report

Cell Formulas
Range	Formula
B2:B31	B2	=INDEX(Incidents!B:B,MATCH(report!A2,Incidents!A:A,0))

and here is the sheet that contains the values i would like to lookup (very simplified of course)

Question.xlsx
	A	B	C	D
1	username
2	User_1	2
3	User_2
4	User_3	2
5	User_4
6	User_5
7	User_6
8	User_7	4
9	User_8
10	User_9
11	User_10
12	User_11
13	User_12
14	User_13
15	User_1	1		Answer I am looking for
16	User_2	11
17	User_16
18	User_17
19	User_7	3		Answer I am looking for
20	User_19
21	User_20
22	User_21
23	User_22
24	User_23
25	User_24
26	User_25
27	User_26
28	User_27
29	User_28
30	User_29
31	User_30
32	User_14
33	User_15
34	User_18
Incidents

 

[arthurbr]

Depending on your Excel version the MINIFS function is available. Don't forget to add a condition for empty cells !

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1	username	Lowest priority case
2	User_1	1
3	User_2	0
4	User_3	2
5	User_4	0
6	User_5	0
7	User_6	0
8	User_7	3
9	User_8	0
10	User_9	0
11	User_10	0
12	User_11	0
13	User_12	0
14	User_13	0
15	User_14	0
16	User_15	0
17	User_16	0
18	User_17	0
19	User_18	0
20	User_19	0
21	User_20	0
22	User_21	0
23	User_22	0
24	User_23	0
25	User_24	0
26	User_25	0
27	User_26	0
28	User_27	0
29	User_28	0
30	User_29	0
31	User_30	0
Report

Cell Formulas
Range	Formula
B2:B31	B2	=MIN(IF(Incidents!$A$2:$A$100=A2,Incidents!$B$2:$B$100,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sprinter99]

Yes, that worked perfectly, after some poking about with the data, unfortunately the case priority is text rather than as i stated so where i had a simple number if the Incidents I.E. 1,2 or 3, its actually "3 - Medium", so i needed too create a new column and add "=NUMBERVALUE(LEFT(H20,1))", once i did the lookup on that cell/column it worked a treat.

That could probably be converted into a single process/formula, but its working which is fine.

Thanks you

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Fluff]

You could also use this which won't need the helper column

=MIN(IF(Incidents!$A$2:$A$100=A2,IFERROR(VALUE(LEFT(Incidents!$B$2:$B$100)),0),""))