Conditional lookup based on two variables, with approximation

[Tacos_Tacos_Tacos]

Hello. This one's a doozy. I've had little luck finding answers elsewhere, so perhaps someone here can help.

I have two sheets - Sheet1 and Sheet2. On Sheet1, I have four columns: NAME, CHECK DATE, ELECTION TYPE, and ELECTION AMT. The first two columns are populated, and the latter two are not. The first column will contain names three rows (not merged) at a time. For example, "John Smith" will appear three times in three rows, each with a different date in the DATE column. Our goal is to populate the ELECTION TYPE and ELECTION AMT columns by referencing the data from the first two to find the appropriate data on Sheet2.

Sheet1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Check Date[/TD]
[TD]Election Type[/TD]
[TD]Election Amt.[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]10/31/14[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]11/14/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]11/28/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]10/31/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]11/14/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]11/28/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On Sheet2 I have four columns: NAME, ELECTION TYPE, ELECTION AMT, and EFFECTIVE DATE. Each name may appear any number of times, with varying data under ELECTION TYPE and ELECTION AMT. This is the data we want to pull to Sheet1.

Sheet2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Election Type[/TD]
[TD]Election Amount[/TD]
[TD]Effective Date[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Pre-tax[/TD]
[TD]14%[/TD]
[TD]9/13/14[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Pre-tax[/TD]
[TD]17%[/TD]
[TD]9/14/14[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Roth[/TD]
[TD]20%[/TD]
[TD]10/28/14[/TD]
[/TR]
[TR]
[TD]Phil
[/TD]
[TD]Roth[/TD]
[TD]15%[/TD]
[TD]11/7/14[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Pre-tax[/TD]
[TD]10%[/TD]
[TD]11/10/14[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Roth[/TD]
[TD]14%[/TD]
[TD]11/21/14[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Pre-tax[/TD]
[TD]12%[/TD]
[TD]11/28/14[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]Roth[/TD]
[TD]9%[/TD]
[TD]10/16/14[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]Roth[/TD]
[TD]14%[/TD]
[TD]10/31/14[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]Pre-tax[/TD]
[TD]10%[/TD]
[TD]11/1/14[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]Pre-tax[/TD]
[TD]15%[/TD]
[TD]11/2/14[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]Pre-tax[/TD]
[TD]5%[/TD]
[TD]11/26/14[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]Pre-tax[/TD]
[TD]8%[/TD]
[TD]12/15/14[/TD]
[/TR]
</tbody>[/TABLE]

I need to develop a formula for use in Sheet1 that will allow me to look up the corresponding name on Sheet2, then, referencing CHECK DATE on Sheet1, find the EFFECTIVE DATE on Sheet2 that is closest without going over. So, for example, if we are referencing the first row on Sheet1, we would look on Sheet2 for the rows that have "Phil" in the first column, and then the date closest to 10/31/14 that does not exceed that date. Here, it would be "10/28/14." Finally, I want to populate the latter two columns of Sheet1 with the corresponding data from the appropriate row on Sheet2 ("Roth" and "20%" in this example).

I suspect this will require an array function, and I am not as experienced with that as I'd like. We're dealing with about 100,000 rows here, so any help or insight would be immensely appreciated. I've shopped this around other forums, but have found no good solution yet. Thank you in advance for your time and effort.

 

[Aladin Akyurek]

A:D of Sheet2 houses the data (not shown here, see above).

Sheet1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Check Date[/td][td]Election Type[/td][td]Election Amt.[/td][td]Idx[/td][/tr]

[tr][td]

2​

[/td][td]Phil[/td][td]

10/31/2014​

[/td][td]Roth[/td][td]

0.15​

[/td][td]

4​

[/td][/tr]

[tr][td]

3​

[/td][td]Phil[/td][td]

11/14/2014​

[/td][td]Roth[/td][td]

0.14​

[/td][td]

6​

[/td][/tr]

[tr][td]

4​

[/td][td]Phil[/td][td]

11/28/2014​

[/td][td]Pre-tax[/td][td]

0.12​

[/td][td]

7​

[/td][/tr]

[tr][td]

5​

[/td][td]Elizabeth[/td][td]

10/31/2014​

[/td][td]Roth[/td][td]

0.14​

[/td][td]

9​

[/td][/tr]

[tr][td]

6​

[/td][td]Elizabeth[/td][td]

11/14/2014​

[/td][td]Pre-tax[/td][td]

0.05​

[/td][td]

12​

[/td][/tr]

[tr][td]

7​

[/td][td]Elizabeth[/td][td]

11/28/2014​

[/td][td]Pre-tax[/td][td]

0.08​

[/td][td]

13​

[/td][/tr]
[/table]

C2, copied to D2 and down:

=IF(ISNUMBER($E2),INDEX(Sheet2!B$2:B$14,$E2),"")

E2, control+shift+enter, not just enter, and copy down:

=MATCH(1,IF(Sheet2!$A$2:$A$14=$A2,IF($B2<=Sheet2!$D$2:$D$14,1)),0)

 

[Tacos_Tacos_Tacos]

Thank you from the bottom of my soul for taking a look at this.

Unfortunately, these formulas aren't quite pulling the correct data. The table ​should look as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Check Date[/TD]
[TD]Election Type[/TD]
[TD]Election Amt.[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]10/31/14[/TD]
[TD]Roth[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]11/14/14[/TD]
[TD]Pre-tax[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]11/28/14[/TD]
[TD]Pre-tax[/TD]
[TD]12%[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]10/31/14[/TD]
[TD]Roth[/TD]
[TD]14%[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]11/14/14[/TD]
[TD]Pre-tax[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]11/28/14[/TD]
[TD]Pre-tax[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]

It appears the dates being pulled aren't the ones we're looking for. To be clear, I need the formula to reference the CHECK DATE and then find the row with the closest EFFECTIVE DATE that does not exceed the CHECK DATE. For Phil's second row (11/14/14), for example, it should pull data from Phil's fifth row on Sheet2 (11/10/14), as 11/10/14 is the closest date to 11/14/14 that does not go beyond it.

I really appreciate your help.

 

[Aladin Akyurek]

Thank you from the bottom of my soul for taking a look at this.

Unfortunately, these formulas aren't quite pulling the correct data. The table ​should look as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Check Date[/TD]
[TD]Election Type[/TD]
[TD]Election Amt.[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]10/31/14[/TD]
[TD]Roth[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]11/14/14[/TD]
[TD]Pre-tax[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]11/28/14[/TD]
[TD]Pre-tax[/TD]
[TD]12%[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]10/31/14[/TD]
[TD]Roth[/TD]
[TD]14%[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]11/14/14[/TD]
[TD]Pre-tax[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]Elizabeth[/TD]
[TD]11/28/14[/TD]
[TD]Pre-tax[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]

It appears the dates being pulled aren't the ones we're looking for. To be clear, I need the formula to reference the CHECK DATE and then find the row with the closest EFFECTIVE DATE that does not exceed the CHECK DATE. For Phil's second row (11/14/14), for example, it should pull data from Phil's fifth row on Sheet2 (11/10/14), as 11/10/14 is the closest date to 11/14/14 that does not go beyond it.

I really appreciate your help.

Modify the formula in E2...

=MATCH($B2,IF(Sheet2!$A$2:$A$14=$A2,Sheet2!$D$2:$D$14),1)

which must be confirmed with control+shift+enter, not just enter, and copied down.

 

[Tacos_Tacos_Tacos]

Right about the time you posted I managed to put the following together, which I believe is working correctly:

=MATCH(1,IF(Sheet2!$A$2:$A$14=$A2,IF($B2>=Sheet2!$D$2:$D$14,1)),1)

Again, thank you very much for your help.

If anyone has the time and inclination, I am very curious how the array (ctrl+shift+enter) function is working here. Any kind of explanation would be quite useful.

 

[Aladin Akyurek]

Modify the formula in E2...

=MATCH($B2,IF(Sheet2!$A$2:$A$14=$A2,Sheet2!$D$2:$D$14),1)

which must be confirmed with control+shift+enter, not just enter, and copied down.

Right about the time you posted I managed to put the following together, which I believe is working correctly:

=MATCH(1,IF(Sheet2!$A$2:$A$14=$A2,IF($B2>=Sheet2!$D$2:$D$14,1)),1)

Again, thank you very much for your help.

If anyone has the time and inclination, I am very curious how the array (ctrl+shift+enter) function is working here. Any kind of explanation would be quite useful.

In:

=MATCH($B2,IF(Sheet2!$A$2:$A$14=$A2,Sheet2!$D$2:$D$14),1)

the IF bit collects all dates from the records where the name range = Phil...

{41895;41896;41940;41950;41953;41964;41971;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

FALSE obtains when Fhil not hold.

The MATCH bit matches the criterion/condition date against the foregoing ascending order set of dates with match-type set to 1 (TRUE). The successful match results in a relative rown number which INDEX uses for retrieval.

The version with 1 as look up value creates an array of 1 and FALSE values against which an approximate match is done as above.

 

[Tacos_Tacos_Tacos]

This is great information. Thanks.

A follow-up: would it be possible to use embedded MATCH functions to increase the number of variables with which we look up our data?

 

[Aladin Akyurek]

This is great information. Thanks.

You are welcome.

A follow-up: would it be possible to use embedded MATCH functions to increase the number of variables with which we look up our data?

All things being equal, yes.