Match and inext multiple criteria

[Kare1]

Im struggling with a formula, one match is an exact match the other is the closest less than. I can get the formular to work with the individual matches but I am struggling to get it to combine the 2 together, I end up wit the REF error

{=INDEX(B2:G30,MATCH("top",G2:G30,0),MATCH(B8,B2:B30,1),3)}

G2:G30 is a list of words (Top, Bottom) - This has to be an exact match
B8:B30 is a list of dates / Times eg 01/08/2017 12:50 - This has to be the closest before the date / time in Cell B8
3 is the cell I want it to return

 

[steve the fish]

This is a bit confused. Could you post the data? Make it so we can paste it back into excel.

 

[Scott T]

You are not using index correctly your match functions are returning row numbers but you are putting the second one into the column argument of index. There is no 4th argument where you have 3. Index returns the value from Array based on the row/column number.
=index(array,row number,[column number])

As Steve said it would help to see your data.

 

[steve the fish]

Maybe take a guess at this:

=INDEX(D2:D30,MATCH(1,INDEX((G2:G30="top")*(B2:B30 < B8)*(B2:B30 <> ""),0),0))<b8)*(b2:b30<>

Not sure why one of your criteria is within its range to search though.</b8)*(b2:b30<>

 

[Kare1]

My data basically below, im wanting to be able to put in a date and time and return the result reel number. I can get this to work using index and match but I need to take into consideration top or bottom

eg on the 24th @ 15:40
Top reel number would be 3170867415
and bottom would be 3170812212

Start (Splice) Date & Time	Date	Reel number	Gramage	Weight (of Reel)	Top Or Bottom Paper
23/08/2017 00:29	23/08/2017	3170866825	180	2394	top
23/08/2017 02:58	23/08/2017	3170862713	190	2580	Bottom
23/08/2017 04:51	23/08/2017	3170868221	180	2457	top
23/08/2017 06:20	23/08/2017	3170863424	190	2519	Bottom
23/08/2017 08:25	23/08/2017	3170711623	180	2421	top
23/08/2017 09:30	23/08/2017	3170863422	190	2524	Bottom
23/08/2017 11:50	23/08/2017	3170867721	180	2440	top
23/08/2017 15:18	23/08/2017	3170867613	180	2438	top
23/08/2017 15:28	23/08/2017	3170811613	190	2608	Bottom
23/08/2017 18:50	23/08/2017	3170711713	180	2408	top
23/08/2017 19:21	23/08/2017	3170812521	190	2574	Bottom
23/08/2017 22:17	23/08/2017	3170710421	180	2452	top
23/08/2017 22:41	23/08/2017	3170812513	190	2629	Bottom
23/08/2017 22:45	23/08/2017	3170863314	190	2604	Bottom
24/08/2017 01:46	24/08/2017	3170711211	180	2423	top
24/08/2017 02:05	24/08/2017	3170812623	190	2566	Bottom
24/08/2017 05:15	24/08/2017	3170868014	180	2439	top
24/08/2017 05:25	24/08/2017	3170812524	190	2567	Bottom
24/08/2017 08:50	24/08/2017	3170868024	180	2434	top
24/08/2017 08:50	24/08/2017	3170812614	190	2628	Bottom
24/08/2017 12:15	24/08/2017	3170812511	190	2638	Bottom
24/08/2017 12:20	24/08/2017	3170866824	180	2408	top
24/08/2017 15:30	24/08/2017	3170867415	180	2377	top
24/08/2017 15:35	24/08/2017	3170812212	190	2606	Bottom
24/08/2017 19:00	24/08/2017	3170812313	190	2623	Bottom
24/08/2017 19:03	24/08/2017	3170867411	180	2397	top
24/08/2017 22:39	24/08/2017	3170812412	190	2631	Bottom
24/08/2017 22:47	24/08/2017	3170867413	180	2394	top

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

3170867415

<tbody>
</tbody>

</body>

 

[steve the fish]

As your date/times are in order this works:

=LOOKUP(2,1/((B2:B29< B8)*(G2:G29="top")),D2:D29)<b8)*(g2:g29="top")),d2:d29)< html=""></b8)*(g2:g29="top")),d2:d29)<>

 

[Kare1]

As your date/times are in order this works:

=LOOKUP(2,1/((B2:B29< B8)*(G2:G29="top")),D2:D29)<b8)*(g2:g29="top")),d2:d29)< html=""></b8)*(g2:g29="top")),d2:d29)<>

Wow it works, what do the 2 & 1 at the beginning mean?

 

[Scott T]

 =LOOKUP(2,[COLOR=#FF0000]1/((B2:B29< B8)*(G2:G29="top"))[/COLOR],D2:D29)

The 2 is the lookup value. The part in red is 1 divided by the criteria this gives you the 1 when the criteria is met and div/0 error when the criteria is not met.
Since the lookup value is larger then the numbers in the lookup vector and lookup only does approximant match it returns the last based on the last row that meets the criteria