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

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 400"]
<tbody>[TR]
[TD]3170867415[/TD]
[/TR]
</tbody>[/TABLE]
</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