## Find the most urgent work orders

*Article 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 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 […]### One Response to “Find the most urgent work orders”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Wonderful