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)
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

PaulUR1

New Member
Joined
Jul 24, 2019
Messages
5
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,464
Office Version
365
Platform
Windows
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
 

PaulUR1

New Member
Joined
Jul 24, 2019
Messages
5
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,464
Office Version
365
Platform
Windows
No, those are correct, they just there for safety. :)
 

PaulUR1

New Member
Joined
Jul 24, 2019
Messages
5
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)
 

PaulUR1

New Member
Joined
Jul 24, 2019
Messages
5
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,464
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,002
Messages
5,484,127
Members
407,431
Latest member
kalvinswisher

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top