Question: My initial question was answered here: 
[Looking up a value in a range that is between two given values](https://stackoverflow.com/questions/31004331/looking-up-a-value-in-a-range-that-is-between-two-given-values)

But when trying to achieve the same result with more data rows than just one, I am struggling. Here is a screen shot :

![enter image description here](https://i.stack.imgur.com/yvLOv.jpg)

So, if I type 542515 in, it needs to look at P5 and P6 / Q5 and Q6
and see that 542515 is job 8584 and therefore in the output it must show 8584. If I input between 544360 and 544400 then it needs to output 8586.

Hope this makes sense!

Answer: 
If your ranges in columns `P` and `Q` do *not* overlap, then you can simply check the input value in `N5` against each range using an `IF` formula. My `IF` formula defaults to a value of 0 if the input cannot be found in the range. Then I `SUM` over the `O` column to find the job which matched.

![enter image description here](https://i.stack.imgur.com/CAXs5.png)

**Formula in O5:**

```
=IF(AND(N$5 > P5, N$5 < Q5), M5, 0)

```

**Formula in O6:**

```
=IF(AND(N$5 > P6, N$5 < Q6), M6, 0)

```

**Formula in O8:**

```
=SUM(O5:O6)

```
you can use `SUMIFS` formula if the ranges does not overlap

Example considering 544370 is mentioned in N3 cell
`O3=SUMIFS(M:M,P:P,"<="&N3,Q:Q,">="&N3)` 

For non-numeric job value try this

`=INDEX(M:M,SUMPRODUCT(--(P:P<=N3),--(Q:Q>=N3),ROW(A:A)))`