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

Shift	TimeStamp		Job ID	Qty
Night	24/11/2017 06:39		00602531	27990
Day	24/11/2017 15:11		00602531	26434
Evening	24/11/2017 22:34		00602531	25678
Night	25/11/2017 06:28		00602531	26661
Day	25/11/2017 14:39		00602455	22323
Evening	25/11/2017 22:30		00602455	26513
Evening	26/11/2017 22:59		00602455	1580
Night	27/11/2017 05:29		00602455	24975
Day	27/11/2017 07:29		00602455	25389
Evening	27/11/2017 22:30		00602455	24861
Night	28/11/2017 06:58		00602455	27639
Day	28/11/2017 14:41		00602455	23564
Evening	28/11/2017 22:42		00602455	26612
Night	29/11/2017 05:17		00602455	23101

<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> </tbody>

 

[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.