# Thread: Return a result from an overall array but limit by another value Thanks:  1 Post #5314419 (1) Likes:  2 Post #5316616 (1)Post #5314419 (1)

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

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)

2. ## Re: Return a result from an overall array but limit by another value

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!

3. ## Re: Return a result from an overall array but limit by another value

Hi & welcome to MrExcel.
=INDEX(\$B\$2:\$B\$13,MATCH(C27&"|"&\$A27,\$C\$2:\$C\$13&"|"&\$A\$2:\$A\$13,0),1)

Array entered

4. ## Re: Return a result from an overall array but limit by another value

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?

Paul

5. ## Re: Return a result from an overall array but limit by another value

No, those are correct, they just there for safety.

6. ## Re: Return a result from an overall array but limit by another value

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)

7. ## Re: Return a result from an overall array but limit by another value

Originally Posted by PaulUR1
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

8. ## Re: Return a result from an overall array but limit by another value

You're welcome & thanks for the feedback

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•