# Lookup function with with three conditions. Two are fixed and the third is variable and c

1. ## Lookup function with with three conditions. Two are fixed and the third is variable and c

Good Afternoon:

See attachment.

Step One: TWO Cell Exact Match. Seek formula in yellow highlighted cells in Table 1 column A and B items for an exact match to items within Table 2 column I and J.
Step Two: Conditional Lookup. Then enter soonest the time from column K exceeding or equalling column C time but by not more than one minute.
Use the N:7 Cell as I may need to change the time number from time to time.
Enter Zero (0) if there is no match meeting these conditions.

Any way to do this without decending sort, or with standard non array formula?

Thanks

2. ## Re: Lookup function with with three conditions. Two are fixed and the third is variable a

Hi winship,

why your manual result is 0:00:00 in D13 ? According to me, it should be 8:30:17. Please check. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

3. ## Re: Lookup function with with three conditions. Two are fixed and the third is variable a

hi winship, spent quite a bit of time in this to make it non-array. try this in E13:
Formula:
`Please Login or Register  to view this content.`

like dilipandey, i have the same question. 8:12:31 AM is more than 1 minute from 8:11:30 AM & shouldnt be shown

4. ## Re: Lookup function with with three conditions. Two are fixed and the third is variable a

Good Morning:

With respect to D13 being 0:00:00. The value in C13 is 14:29:00. Visual displays no value equal to or exceeding 14:29:00 and less than one minute within table 2 column J. 8:30:17 has a match for columns H and I, but the times are less than 14:29:00.

Benishiryo has found what is a visual scanning error in D27. My template posts a number (08:12:31) that is 00:01:01 (greater than one minute) over 08:11:30. My regrets and that's why I need you guys!

I will punch the benishiryo formula into my original for proofing. It may be that an array would be less complicated. Still learning about array rules as data changes, cell copy and macro use. Maybe it's ok?Maybe not a problem.

Thanks again.

5. ## Re: Lookup function with with three conditions. Two are fixed and the third is variable a

Good Morning:

With respect to D13 being 0:00:00. The value in C13 is 14:29:00. Visual displays no value equal to or exceeding 14:29:00 and less than one minute within table 2 column J. 8:30:17 has a match for columns H and I, but the times are less than 14:29:00.

Benishiryo has found what is a visual scanning error in D27. My template posts a number (08:12:31) that is 00:01:01 (greater than one minute) over 08:11:30. My regrets and that's why I need you guys!

I will punch the benishiryo formula into my original for proofing. It may be that an array would be less complicated. Still learning about array rules as data changes, cell copy and macro use. Maybe it's ok?Maybe not a problem.

Thanks again.

6. ## Re: Lookup function with with three conditions. Two are fixed and the third is variable a

Formula works perfectly. Even found another visually generated error in row 28, row D. I had entered 08:30:17. The correct answer provided by formula is 08:29:39.

Just excellant. Thank you so very much. I will mark this as solved, and you solved it despite my errors.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1