Vlookup IF Min Max

[melq64]

Hi guys, would appreciate any help with this please.

Trying to return min and max for values for blank (?) columns below.

So here's the logic: Table 1
IF Type column="Bulk", then Vlookup Transponder column and match to Table 2 Transponder column and return min date

Note: Transponder value can have multiple Registration Times against it


[TABLE="width: 500"]
<tbody>[TR]
[TD]TABLE 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Transponder[/TD]
[TD]Type[/TD]
[TD]Min Registration Time[/TD]
[TD]Max Registration Time[/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="class: xl65, width: 101"]11001026934[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bulk[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="width: 102"]11001026937[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Clearance[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="width: 102"]11001026937[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bulk[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]TABLE 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Transponder[/TD]
[TD]Registration Time[/TD]
[/TR]
[TR]
[TD]11001026934[/TD]
[TD]28/05/2018 18:52
[/TD]
[/TR]
[TR]
[TD]11001026934[/TD]
[TD]28/05/2018 19:02[/TD]
[/TR]
[TR]
[TD]11001026934[/TD]
[TD]28/05/2018 15:02[/TD]
[/TR]
[TR]
[TD]11001026937[/TD]
[TD]25/05/2018 11:32[/TD]
[/TR]
[TR]
[TD]11001026937[/TD]
[TD]25/05/2018 9:32[/TD]
[/TR]
[TR]
[TD]11001026937[/TD]
[TD]25/05/2018 11:55[/TD]
[/TR]
</tbody>[/TABLE]

 

[MUKESHY12390]

I have uploaded excel file, perhaps you are looking for.

in first sheet (min_max_with IF) I have used ARRAY formula (after you enter formula you have to press CTR+SHIFT+ENTER instead of just ENTER)

Download Here...

 

[MUKESHY12390]

after two days this link will expire.

 

[melq64]

Thanks for this and it works and I see that you separated Registration Time columns BUT the Registration Times for my dataset are in the same column.
Are you able to provide modified formula for this please?

 

[MUKESHY12390]

I'm sorry i didn't get that, could you be more specific.

 

[melq64]

Sorry for the delay in response but thank you for your help, truly appreciate it.

The solution you provided works well however now that my dataset is getter bigger as I append the dataset on a daily basis, it now takes a long time for the formulas to update.

Do you have any suggestions to speed this process up?


Thanks in advance

 

[Aladin Akyurek]

Let A:B of Sheet2 house the Table 2 data.

Let A:D of Sheet1 house the processing.

In C2 of Sheet1 enter and copy down:

=MINIFS(Sheet2!B:B,Sheet2!A:A,A2)

In D2 of Sheet1 enter and copy down:

=MAXIFS(Sheet2!B:B,Sheet2!A:A,A2)

 

[melq64]

Thanks, much appreciated

 

[Aladin Akyurek]

Thanks, much appreciated

You are welcome.​