Return a result from an overall array but limit by another value

PaulUR1

New Member
Joined
Jul 24, 2019
Messages
5
This might sound complicated but that is because I am not really up on the lingo - sorry!!
I have workseets - the first has the following data:

A B C

1 Area Error type Total
2 Bradford Food 4
3 Bradford Cleaning 7
4 Bradford Timing 2
5 Bradford Shift 3
6 Dover Food 1
7 Dover Cleaning 4
8 Dover Timing 2
9 Dover Shift 0
10 Leyland Food 22
11 Leyland Cleaning 2
12 Leyland Timing 3
13 Leyland Shift 1

The actual table is much bigger than this but you get the idea. What I want to do is a separate table that Identifies the top 2 issues for an area and puts them in a table ie:

A B C D E
26 Area Highest Error type Highest error value 2nd Highest Error Type 2nd Highest Error Value
27 Leyland Food 22 Timing 3
28 Bradford Cleaning 7 Food 4

If i use the following formula the correct values appear under Highest error value and second highest

=LARGE(IF($A$2:$A$13=Report!$A27, $c$2:$c$13),1) ****IN CELL C27)
=LARGE(IF($A$2:$A$13=Report!$A27, $c$2:$c$13),2) ****IN CELL E27

the problem comes when I want to automatically input the associated error type. I have used:

=INDEX($A$2:$A$13,MATCH(C27,$C$2:$C$13,0),1)

Initially it looked like this had worked, but then I noticed that is searches for the correct number and returns the error type next to the first occasion of that number in C2:C13 - For the example above - Under the Leyland 2nd highest error type it actually returns "Shift" as in the list C2:C13 that is the first time the number 3 occurs .... It should return 'Timing' as this is the second highest number for Leyland.

Can anyone at all help - this is the last rung on a spreadsheet that will create its own reports for the charity I work for but I just cant get it to only look at the part of the array that relates to the Area given (eg Cell A27 - Leyland)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Ive just noticed that is has ditched my spacing, The ABC at the top of the example tables relate to column A B and C; the 1 - 13 and 26-28 relate to the row numbers!
 
Upvote 0
Hi & welcome to MrExcel.
How about
=INDEX($B$2:$B$13,MATCH(C27&"|"&$A27,$C$2:$C$13&"|"&$A$2:$A$13,0),1)

Array entered
 
Upvote 0
sorry - what are the characters for the second part - it look like:
MATCH(C27&"|"&$A27 ....

Is that correct or is something wrong in my display settings?

Many thanks for your speedy reply

Paul
 
Upvote 0
No, those are correct, they just there for safety. :)
 
Upvote 0
Hi - Sorry to be a pest - this has returned #Value
Can I send the actual spreadsheet to you? it is spread over 2 worksheets (ie table 1 is on one worksheet and table 2 on another - but I referenced these correctly)
 
Upvote 0
Hi - Sorry to be a pest - this has returned #Value
Can I send the actual spreadsheet to you? it is spread over 2 worksheets (ie table 1 is on one worksheet and table 2 on another - but I referenced these correctly)

Sorry - Guess what I forgot ... CTRL+Shift+Enter .... it works ... I had no idea you could match multiple lines so easily - Thank you
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top