asked on

Evaluate Criteria

I have a column D, that has an abbreviated time in this format hhZ, (12Z, 15Z, 18Z, 21Z, and so on) I need to evaluate each row starting from D5 that is closest to the time on Sheet 2, A1. make column Z=true, else false. ONce you found true then make everything below that true. I have attached a worksheet
SAMPLE.xlsx

Danny Child

OK, there's 3 stages to this
1 - find the closest match between your target value and your source range.
2 - search the range to find this value
3 - then fill subsequent formulae with TRUE from that point down.

I think the options here for
=MATCH(MIN(ABS(A1:Z1-A2)),ABS(A1:Z1-A2),0))
Confirm with CTRL+SHIFT+ENTER
will get there, but still working on it, perhaps with some helper rows to remove your Z character.
However, gotta go now, can come back later...

http://www.mrexcel.com/forum/excel-questions/629441-how-find-closest-value-range-given-lookup-value.html

macone1976

ASKER

I column Z i removed the "Z" character

aikimark

Look at the formulas in AA:AB of the attached workbook.
SAMPLE--3-.xlsx

macone1976

ASKER

aikimark, thanks I did have one additional thing I thought I could use that sample formula for other data on the page but it is not working. I have about 5 sets of data. The new data started on row 51, but the formula does not work with that new set of data. Any ideas
Copy-of-SAMPLE--3-.xlsx

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

So did we not resolve this in this question:

https://www.experts-exchange.com/questions/28628678/Copy-Data-from-Criteria.html

With formula:

=OR(Z5>=MROUND(Sheet6!$A$1,3),COUNTIF(AA$4:AA4,TRUE)>0)

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.