Multiple lookup

[robrobby]

Hi, I've been trying to figure out the fomulas to do the following:


I have the data below and what I want to do is a lookup on multiple criteria to fill in the cells on another sheet.
On my other sheet I have sections split into days as here:

A1 Date: 27/11/2017

A2 Night : 24975
A3 Day : 25389
A4 Evening :24861

I want to be able to do a lookup based on the date above and fill in the 3 qty values for night, day, evening for whatever I change the date to in cell A1
I've tried array lookups with no result (ctrl+shift+enter)

I've tried looking up the one date which works fine, and then offsetting the values below, but there are some dates which only have one entry, like the 26/11/17 it only has the evening slot, so this makes other things I try not work either. I need to be able to do a lookup on Shift+TimeStamp
If I did a test lookup with the word "Test" in column B on the data below I could get the qty, but the date/time in the TimeStamp column just doesn't work on just the date..

Any ideas on how to get this working???

Thanks
[TABLE="width: 247"]
<colgroup><col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody>[TR]
[TD="width: 65"]Shift[/TD]
[TD="class: xl65, width: 111, align: right"]TimeStamp[/TD]
[TD="class: xl65, width: 111, align: right"][/TD]
[TD="width: 74"]Job ID[/TD]
[TD="width: 78, align: right"]Qty[/TD]
[/TR]
[TR]
[TD="width: 65"]Night[/TD]
[TD="class: xl65, width: 111, align: right"]24/11/2017 06:39[/TD]
[TD="class: xl65, width: 111, align: right"][/TD]
[TD="width: 74"]00602531[/TD]
[TD="width: 78, align: right"]27990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Day[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]24/11/2017 15:11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"]00602531[/TD]
[TD="bgcolor: transparent, align: right"]26434[/TD]
[/TR]
[TR]
[TD]Evening[/TD]
[TD="class: xl65, align: right"]24/11/2017 22:34[/TD]
[TD="class: xl65, align: right"][/TD]
[TD]00602531[/TD]
[TD="align: right"]25678[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Night[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25/11/2017 06:28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"]00602531[/TD]
[TD="bgcolor: transparent, align: right"]26661[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD="class: xl65, align: right"]25/11/2017 14:39[/TD]
[TD="class: xl65, align: right"][/TD]
[TD]00602455[/TD]
[TD="align: right"]22323[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Evening[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25/11/2017 22:30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"]00602455[/TD]
[TD="bgcolor: transparent, align: right"]26513[/TD]
[/TR]
[TR]
[TD]Evening[/TD]
[TD="class: xl65, align: right"]26/11/2017 22:59[/TD]
[TD="class: xl65, align: right"][/TD]
[TD]00602455[/TD]
[TD="align: right"]1580[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Night[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27/11/2017 05:29[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"]00602455[/TD]
[TD="bgcolor: transparent, align: right"]24975[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD="class: xl65, align: right"]27/11/2017 07:29[/TD]
[TD="class: xl65, align: right"][/TD]
[TD]00602455[/TD]
[TD="align: right"]25389[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Evening[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]27/11/2017 22:30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"]00602455[/TD]
[TD="bgcolor: transparent, align: right"]24861[/TD]
[/TR]
[TR]
[TD]Night[/TD]
[TD="class: xl65, align: right"]28/11/2017 06:58[/TD]
[TD="class: xl65, align: right"][/TD]
[TD]00602455[/TD]
[TD="align: right"]27639[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Day[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]28/11/2017 14:41[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"]00602455[/TD]
[TD="bgcolor: transparent, align: right"]23564[/TD]
[/TR]
[TR]
[TD]Evening[/TD]
[TD="class: xl65, align: right"]28/11/2017 22:42[/TD]
[TD="class: xl65, align: right"][/TD]
[TD]00602455[/TD]
[TD="align: right"]26612[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Night[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]29/11/2017 05:17
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"]00602455[/TD]
[TD="bgcolor: transparent, align: right"]23101[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, here is one way that you could try to adapt to your set-up.

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Shift	TimeStamp		Job ID	Qty		Date:	27/11/2017
2	Night	24/11/2017 06:39		602531	27990		Night	24975
3	Day	24/11/2017 15:11		602531	26434		Day	25389
4	Evening	24/11/2017 22:34		602531	25678		Evening	24861
5	Night	25/11/2017 06:28		602531	26661
6	Day	25/11/2017 14:39		602455	22323
7	Evening	25/11/2017 22:30		602455	26513
8	Evening	26/11/2017 22:59		602455	1580
9	Night	27/11/2017 05:29		602455	24975
10	Day	27/11/2017 07:29		602455	25389
11	Evening	27/11/2017 22:30		602455	24861
12	Night	28/11/2017 06:58		602455	27639
13	Day	28/11/2017 14:41		602455	23564
14	Evening	28/11/2017 22:42		602455	26612
15	Night	29/11/2017 05:17		602455	23101
Sheet1

Cell Formulas
Range	Formula
H2	=INDEX($E$2:$E$15,MATCH(1,INDEX((INT($B$2:$B$15)=H$1)*($A$2:$A$15=G2),0),0))

 

[robrobby]

That is exactly what I needed - I see how you've converted the date to an integer, I never thought of that. I tried variations of the index/match lookups but could never get it to work like yours does.

I can use this in so many other projects.

Thanks so much, it's really appreciated.