## 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
- Press with left mouse button on "Special"
- Press with left mouse button on "Blanks"
- Press with left mouse button on "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 press with left mouse button on 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

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

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

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 any number of criteria

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

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 […]

### One Response to “Find entry based on conditions”

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

I have a table Columns A:L with the following headings: Departure Airport, Gate, Destination Airport, Gate, Airline, AICO, Call Sign, Flight No., Country, Distance. I have up to 1000 rows.

on a separate tab, I want to create a list based on the departing and arriving airport: the list should contain departing airport, destination airport, which airline and the flight number. Ie.: if i type in my query depart New York and arrive in Montreal, i want the result to show me which airline and flight number can do that flight.

Possible to do?

Marcel