# nested if statement based on a range of dates and times

#### Thequestion

##### New Member
Dear reader, I'm having a problem with a nested if statement. Based on the dates in my first tab, I search in the range of dates in my second tab, if true, then I do the same thing with the times in the same formula, if true I want the corresponding value of my second tab, otherwise a blank ("") but each cell should consist of a value with a correct formula.

This worked: =
 IF(A2=Spot!A3,IF(B2=Spot!L1,Spot!L3,""),"") A2 & SpotA3 being 17/02/2016 B2 and L1 being 11:00 L3 being the corresponding correct value However, with the above formula I need to find all corresponding values myself 1 by 1. This formula didn't work: =IF(A2=Spot!A:A,IF(Tab1!B2=Spot!1:1,Spot!B2:Y415,""),"") Spot!B2:Y415: being the range in which the correct value is

<tbody>
</tbody>

 First tab Time Stamp 17/02/2016 11:00:00

<tbody>
</tbody>

Second tab

 Time interval 01:00:00 02:00:00 18/02/2016 20.74 20.57 17/02/2016 18.97 18.86

<tbody>
</tbody>
I also tried to solve this problem with match without success.

Hopefully someone can help me so I can help others as well in the future

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try

=INDEX(Spot!\$B\$2:\$Y\$415,MATCH(A2,Spot!\$A\$2:\$A\$415,0),MATCH(B2,Spot!\$B\$1:\$Y\$1,0))

Sorry for the late reply. It works, thank you very much!

Sorry for the double post, it only partly works. What could be the problem?23.5329.9531.71#N/A#N/A19#N/A#N/A#N/A

Replies
1
Views
349
Replies
3
Views
127
Replies
5
Views
198
Replies
2
Views
286
Replies
0
Views
197

1,216,483
Messages
6,130,932
Members
449,607
Latest member
babylegs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back