Lookup closest match in a limited/filtered list

[langvik]

Dear all,
in Excel 2003,

I need to lookup the closest date match in a list, but only for the rows (records) that matches a specified cell.

I have a list consisting of a column (A) with Birds_name, and a column with the Observation_date (B). I would like to find the closest match of the date for observation of e.g. a seagull in the list. Hence I have a input field for both Bird_name_input and Date_input. I would like to retrieve the date of the Bird_name_input observed that is the closest match to the specified date in the Date_input cell.
I found that I can do a closest match lookup via this formula: {=index(B2:B97;MATCH(MIN(ABS(B2:B97-Date_Input));ABS(B2:B97-Date_input)0);1)}
But this will lookup closest match for the date on all bird observations in the list. I would like help to limit the search for only the seagull observations in the list (include filter for column A).

How may this be done?

Preferably I will avoid VBA and only have it done by a formula in a cell, without having to trigg a filter of the list for each time a new observation is added.

Thanks,
langvik

 

[Aladin Akyurek]

Not sure whether you are autofiltering the data area...


Control+shift+enter, not just enter:

AutoFilter activated...

=INDEX(B2:B97,MATCH(MIN(IF(SUBTOTAL(3,OFFSET(A2:A97,
    ROW(A2:A97)-ROW(A2),,1)),IF(A2:A97=Bird_name_input,
      ABS(B2:B97-Date_input),"#"))),IF(A2:A97=Bird_name_input,
        ABS(B2:B97-Date_input)),0))

No AutoFilter...

=INDEX(B2:B97,MATCH(MIN(IF(A2:A97=Bird_name_input,
    ABS(B2:B97-Date_input),"#")),IF(A2:A97=Bird_name_input,
      ABS(B2:B97-Date_input)),0))

 

[langvik]

Thanks a lot! It does exactly what I needed (I only have tried the autofilter one).

I also want to report from another column (column C, "Location") on the same row/from same record. The row is already found by the previous formula, but I don't understand how to retrieve it, and use an offset to get the result from the same row, but one cell to the right. How may this be done?

Thanks

 

[Aladin Akyurek]

Thanks a lot! It does exactly what I needed (I only have tried the autofilter one).

Great. Thanks for providing feedback

I also want to report from another column (column C, "Location") on the same row/from same record. The row is already found by the previous formula, but I don't understand how to retrieve it, and use an offset to get the result from the same row, but one cell to the right. How may this be done?

Thanks

Either...

You can replace B2:B97 with C2:97.

Or...

X1, control+shift+enter, not just enter:

=MATCH(MIN(IF(SUBTOTAL(3,OFFSET(A2:A97,
   ROW(A2:A97)-ROW(A2),,1)),IF(A2:A97=Bird_name_input,
     ABS(B2:B97-Date_input),"#"))),IF(A2:A97=Bird_name_input,
       ABS(B2:B97-Date_input)),0))

Y1, just enter:

=INDEX(B2:B97,X1)

Z1, just enter:

=INDEX(C2:C97,X1)