## Find entry based on conditions

Hello Oscar,

I am building a spreadsheet for tracking calls for my local fire department. I have a column "a" as an incident number. the incident number is a one-time yearly number usage.

Column "c" is apparatus name and there are 1 of 8 possible names may be used in this cell. column "h" has the formula to give me the time spent on the scene.

I am needing help getting sheet 2 to tag the time spent on a call per apparatus. Sheet 2 is the names of personnel on the scene. I want to put the time on scene according to what apparatus they were on for each incident.

example:

column "a" newest entry is #10

column "c" is "bt1" or "bt2" or "e1" or "e3" or "e4" or "e5" or "pov" or "stby"

There often will be multiple rows with the same incident# in column "a" but different apparatus in column "c".

Column "h" will have on scene time calculated by "=f5-d5"(for that row)

I need to tag on the scene time from sheet 1 column "h" to the corresponding incident number column "a" according to the apparatus column "c".

Last Total

Enroute Arrival Clear Response Incident

Incident # Date Apparatus Time Time Time Time Time

1 03/01/12 bt2 8:18 8:27 18:45 0:09:00 10:27:00

2 03/25/12 bt2 8:20 8:23 17:45 0:03:00 9:25:00

e1 17:05 17:10 17:45 0:05:00 0:40:00

e3 12:33 12:38 17:45 0:05:00 5:12:00

3 03/26/12 e4 7:45 8:08 10:22 0:23:00 2:37:00

4 03/26/12 bt2 11:14 11:16 11:29 0:02:00 0:15:00

5 03/27/12 pov 13:10 13:20 18:36 0:10:00 5:26:00

stby 13:15 13:20 18:36 0:05:00 5:21:00

bt1 13:15 13:20 18:36 0:05:00 5:21:00

bt2 13:16 13:21 18:36 0:05:00 5:20:00

6 03/28/12 e1 8:18 8:27 18:45 0:09:00 10:27:00

e3 8:20 8:30 18:45 0:10:00 10:25:00

7 03/28/12 bt1 8:20 8:23 17:45 0:03:00 9:25:00

e5 9:00 9:03 17:45 0:03:00 8:45:00

8 03/28/12 bt2 9:20 9:22 9:59 0:02:00 0:39:00

9 03/29/12 e1 17:45 17:50 18:00 0:05:00 0:15:00

The array formula reads incident numbers and apparatus values, therefore, I entered missing incident numbers.

**How to enter a formula in blank cells**

- Select cell range A1:A17
- Press F5
- Click "Special"
- Click "Blanks"
- Click "OK"
- Type =A3
- Press and hold Ctrl
- Press Enter

### Array formula in sheet2

Array formula in cell C2:

#### How to enter an array formula

- Double click cell C2
- Copy/Paste array formula
- Press and hold Ctrl and Shift
- Press Enter

#### How to copy array formula

- Select cell C2
- Copy (Ctrl + c)
- Select cell range C3:C10
- Paste (Ctrl + v)

### Explaining array formula in cell C2

#### Step 1 - Find incident number and Apparatus value

(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17)

becomes

("bt2"={"bt2";"bt2";"e1";"e3";"e4";"bt2";"pov";"stby";"bt1";"bt2";"e1";"e3";"bt1";"e5";"bt2";"e1"} ) * (1={1;2;2;2;3;4;5;5;5;5;6;6;7;7;8;9})

becomes

{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

#### Step 2 - Convert array to row numbers

IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")

becomes

IF({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")

becomes

IF({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}, "")

and returns

{1;"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

#### Step 3 - Find smallest value in array

MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), ""))

becomes

MIN({1;"";"";"";"";"";"";"";"";"";"";"";"";"";"";""})

and returns

1

#### Step 4 - Return an error if no value is found

IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")))

becomes

IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), 1))

becomes

IF(SUM({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})=0, NA(), 1))

becomes

IF(1=0, NA(), 1))

becomes

IF(False, NA(), 1))

and returns 1

#### Step 5 - Return time value

=INDEX(Sheet1!$H$2:$H$17, IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), ""))))

becomes

=INDEX(Sheet1!$H$2:$H$17, 1)

becomes

=INDEX({0,435416666666667; 0,392361111111111; 0,0277777777777778; 0,216666666666667; 0,109027777777778; 0,0104166666666667; 0,226388888888889; 0,222916666666667; 0,222916666666667; 0,222222222222222; 0,435416666666667; 0,434027777777778; 0,392361111111111; 0,364583333333333; 0,0270833333333333; 0,0104166666666667}, 1)

and returns 0,435416666666667 in cell C2. Formatted as time, 10:27:00

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Lookup with an unknown number of criteria

Rashid asks:I used your array formula with great success to find the search results from multiple criteria. However, my problem […]

The formula in cell D3 lets you get the last value in column B, it works fine with blank cells […]

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

### 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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form