## Find the most urgent work orders

*Article last updated on February 12, 2014*

This animated picture shows you the most urgent work orders for a location. Type a location in cell F3 and the formula in cell G3 extracts the most urgent value for that particular location. The values in column C are 0 (blank), Low, Medium and High.

I have added conditional formatting to the table so you can quickly verify that the formulas in cell G3 and H3 are correct.

**Array formula in cell G3:**

Array formula in cell H3 can be found in this post: How to return multiple values using vlookup

### Explaining the array formula in cell G3

Cell value in F3 is A in the calculations below.

**Step 1 - Calculate the relative positions of values in column C with these values {0; "Low"; "Medium"; "High"}**

MATCH($C$3:$C$17, {0; "Low"; "Medium"; "High"}, 0) returns {2; 3; 1; 2; 1; 2; 2; 3; 3; 4; 1; 2; 1; 3; 3}

**Step 2 - Extract values matching cell F3**

IF(F3=B3:B17, MATCH($C$3:$C$17, {0; "Low"; "Medium"; "High"}, 0), "") returns {""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 2;""; 3;""}

**Step 3 - Find the largest value**

* *MAX(IF(F3=B3:B17, MATCH($C$3:$C$17, {0; "Low"; "Medium"; "High"}, 0), "")) returns 3.

**Step 4 - Return urgent value**

=INDEX({""; "Low"; "Medium"; "High"}, MAX(IF(F3=B3:B17, MATCH($C$3:$C$17, {0; "Low"; "Medium"; "High"}, 0), ""))) returns Medium in cell G3.

The MATCH and MAX function returns the most urgent value in column C, the order of values in the array determines the importance. {0;"Low";"Medium","High"}. For example, "Medium" is more urgent than "Low". The MATCH function matches one set of values (column C) with another set of values {0;"Low";"Medium","High"}.

That makes the MATCH function really amazing, if you combine multiple MATCH functions you can work with related tables:

- Lookups in a related table (array formula)
- Extract unique distinct values from a related table
- Working with three related tables
- Applying conditional formatting to related tables
- Merge two related tables

### Download excel *.xlsx

Find most urgent work orders.xlsx

### Functions in this post:

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MAX(**number1, number2**)
**Returns the largest value in a set of values

** MATCH(**lookup_value, lookup_array, [match_type

**]**

Returns the relative position of an item in an array that matches a specified value

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Compare two tables: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

Filter common values from three columns

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the […]

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]

Compare two columns and show differences

Array Formula in B11: =INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))) To enter an array formula press and hold CTRL […]

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

### One Response to “Find the most urgent work orders”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Wonderful