Complicated multiple looksups using two separate worksheets

[Traveltime]

I really have no idea where to start here. I have two worksheets and I need to match a Crew IDs in one with the Crew IDs in another. Note, there are multiple rows with Crew IDs in both. For example, There are probably 100 crew IDs in both and multiple rows of each Crew ID in both. I want to search the second worksheet for a Crew ID. Once the rows are found with the Crew ID in the second, I want to search for a date that falls within two days of a date in the first worksheet, and I want to match a $ amount from the first and the second. When all that matches, then I want to paste a cell value in the second worksheet to the first. Ugh, that's a lot.



Example:


Worksheet One: Worksheet Two:

Journal Date Crew ID $ Charged Output- Worksheet Two Posted Date Crew ID $ charged Business Purpose:
1/30/2017 7654 $4.54 Sales Prospect 1/29/2017 7654 $10.00 Client Visit
2/3/2017 8745 $3.00 Client Review 2/4/2017 8745 $5.00 Sales Prospect
1/31/2017 7654 $10.00 Client Visit 1/30/2017 7654 $4.54 Sales Prospect
2/5/2017 8745 $5.00 Sales Prospect 2/1/2017 8745 $3.00 Client Review

 

[Traveltime]

Let me repost a better illustration of what I need. Hopefully this works. I should also add, both worksheets have many more rows and columns

[TABLE="width: 700"]
<tbody>[TR]
[TD]Worksheet 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Worksheet 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Journal D
[/TD]
[TD]Crew ID
[/TD]
[TD]Charge Amt
[/TD]
[TD]Output-Business Purpose
[/TD]
[TD][/TD]
[TD]Date Entered
[/TD]
[TD]Crew ID
[/TD]
[TD]$ Amount
[/TD]
[TD]Business Pupose
[/TD]
[/TR]
[TR]
[TD="align: right"]1/30/2017
[/TD]
[TD="align: right"]7654
[/TD]
[TD="align: right"]$4.54
[/TD]
[TD]Sales Prospect
[/TD]
[TD][/TD]
[TD="align: right"]1/29/2017
[/TD]
[TD="align: right"]7654
[/TD]
[TD] $ 10.00
[/TD]
[TD]Client Visit
[/TD]
[/TR]
[TR]
[TD="align: right"]2/3/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD="align: right"]$3.00
[/TD]
[TD]Client Review
[/TD]
[TD][/TD]
[TD="align: right"]2/4/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD] $ 5.00
[/TD]
[TD]Sales Prospect
[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2017
[/TD]
[TD="align: right"]7654
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD] Client Visit
[/TD]
[TD][/TD]
[TD="align: right"]1/30/2020
[/TD]
[TD="align: right"]7654
[/TD]
[TD] $ 4.54
[/TD]
[TD]Sales Prospect
[/TD]
[/TR]
[TR]
[TD="align: right"]2/5/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD="align: right"]$5.00
[/TD]
[TD]Sales Prospect
[/TD]
[TD][/TD]
[TD="align: right"]2/1/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD] $ 3.00
[/TD]
[TD]Client Review
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Are you sure the first record of Sheet1 should be classified as Sales Prospect?

 

[Aladin Akyurek]

Either: In D2 of Sheet2 just enter and copy down:

=LOOKUP(ROWS(Sheet2!$A$2:$D$5),(ROW(Sheet2!$A$2:$D$5)-ROW(INDEX(Sheet2!$A$2:$D$5,1,1))+1)/((ABS(Sheet2!$A$2:$A$5-$A2)<=2)*(Sheet2!$B$2:$B$5=$B2)*(ROUND(Sheet2!$C$2:$C$5,2)=ROUND($C2,2))),Sheet2!$D$2:$D$5)

Or: In D2 of Sheet1 control+shift+enter, not just enter, and copy down:

=INDEX(Sheet2!$D$2:$D$5,MATCH(B2,IF(ABS(Sheet2!$A$2:$A$5-A2)<=2,IF(ROUND(Sheet2!$C$2:$C$5,2)=ROUND(C2,2),Sheet2!$B$2:$B$5)),0))

 

[Traveltime]

Are you sure the first record of Sheet1 should be classified as Sales Prospect?

It looks like it to me.

Thanks for providing possible solutions. I'll give them a try and let you know if one of them works.

 

[Aladin Akyurek]

It looks like it to me.

Thanks for providing possible solutions. I'll give them a try and let you know if one of them works.

They both work the same way, i.e. identical. Both do array-processing while the second requires control+shift+enter to be active.

 

[Traveltime]

Let me repost a better illustration of what I need. Hopefully this works. I should also add, both worksheets have many more rows and columns

[TABLE="width: 700"]
<tbody>[TR]
[TD]RIG Travel 2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2017 Concur
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] T
[/TD]
[TD] AE
[/TD]
[TD] L
[/TD]
[TD]Output-Business Purpose (AL)
[/TD]
[TD][/TD]
[TD] A
[/TD]
[TD] H
[/TD]
[TD] C
[/TD]
[TD] K
[/TD]
[/TR]
[TR]
[TD="align: right"]1/30/2017
[/TD]
[TD="align: right"]7654
[/TD]
[TD="align: right"]$4.54
[/TD]
[TD]Sales Prospect
[/TD]
[TD][/TD]
[TD="align: right"]1/29/2017
[/TD]
[TD="align: right"]7654
[/TD]
[TD] $ 10.00
[/TD]
[TD]Client Visit
[/TD]
[/TR]
[TR]
[TD="align: right"]2/3/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD="align: right"]$3.00
[/TD]
[TD]Client Review
[/TD]
[TD][/TD]
[TD="align: right"]2/4/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD] $ 5.00
[/TD]
[TD]Sales Prospect
[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2017
[/TD]
[TD="align: right"]7654
[/TD]
[TD="align: right"]$10.00
[/TD]
[TD] Client Visit
[/TD]
[TD][/TD]
[TD="align: right"]1/30/2020
[/TD]
[TD="align: right"]7654
[/TD]
[TD] $ 4.54
[/TD]
[TD]Sales Prospect
[/TD]
[/TR]
[TR]
[TD="align: right"]2/5/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD="align: right"]$5.00
[/TD]
[TD]Sales Prospect
[/TD]
[TD][/TD]
[TD="align: right"]2/1/2017
[/TD]
[TD="align: right"]8745
[/TD]
[TD] $ 3.00
[/TD]
[TD]Client Review
[/TD]
[/TR]
</tbody>[/TABLE]

I labeled the actual columns in the table to make it more clear for you. I used the formula below and received #N/A. Does this formula look correct? I entered the following formula in column AL of RIG Travel 2017. Please note when I ****+Cntrl+Enter, a window entitled "Update values 2017 Concur. Not sure what to do with this.

=INDEX('2017 Concur'!$K$2:$K$63146,MATCH(AE2,IF(ABS('2017 Conccur'!$A$2:$A$63146-T2)<=2,IF(ROUND('2017 Concur'!$C$2:$C$63146,2)=ROUND(L3,2),'2017 Concur'!$H$2:$H$63146)),0))

Here is the formula provided earlier:
=INDEX(Sheet2!$D$2:$D$5,MATCH(B2,IF(ABS(Sheet2!$A$2:$A$5-A2)<=2,IF(ROUND(Sheet2!$C$2:$C$5,2)=ROUND(C2,2),Sheet2!$B$2:$B$5)),0))

TIA

 

[Aladin Akyurek]

Here is the workbook which implements both alternative formulas: https://www.dropbox.com/s/8u89p852g2w152y/Traveltime%20conditional%20look%20up.xlsx?dl=0

 

[Traveltime]

Thank you for providing this. Unfortunately, I'm still having issues with both formulas. Excel comes back with error for the lookup "array" and the Index formula comes back with N/A. Please take a look at my formulas and let me know if they are correct and/or give me further guidance. Thank you so much for your help.


Would if be helpful to post the picture of the actual worksheets with sensitive information changed? If so, what is the best way? I tried to post a copy of the cells, but it didn't carry over very well.

For reference, Column Headings:

RIG Travel 2017 2017 Concur [TABLE="width: 486"]
<tbody>[TR]
[TD]Date A T
[/TD]
[/TR]
[TR]
[TD]Crew ID B AE
[/TD]
[/TR]
[TR]
[TD]Transaction Amt C L
[/TD]
[/TR]
[TR]
[TD]Purpose OUTPUT (AL) K
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Here is the lookup I use:

[TABLE="width: 644"]
<tbody>[TR]
[TD]=Lookup(rows('2017 Concur'!$a$2:$w$63146),(Row('2017 Concur'!$a$2:$w$63146)-row(index(('2017 Concur'!$a$2:$w$63146,1,1))+1)/((ABS(('2017 Concur'!$a$2:$a$63146-$t2)<=2)*('2017 Concur'!$h$2:$h$63146=$ae2)*(Round('2017 Concur'!$c$2:$c$63146,2)=round($l2,2))),'2017 Concur'!$k$2:$k$63146)
[/TD]
[/TR]
</tbody>[/TABLE]


=INDEX('2017 Concur'!$K$2:$K$63146,MATCH(AE6,IF(ABS('2017 Conccur'!$A$2:$A$63146-T6)<=2,IF(ROUND('2017 Concur'!$C$2:$C$63146,2)=ROUND(L7,2),'2017 Concur'!$H$2:$H$63146)),0))

 

[Traveltime]

Well, the table didn't post as it was formatted. Let me try again.

RIG Travel 2017 & 2017 Concur are the worksheet names. For example, the date in RIG Travel 2017 worksheet is located in column T and the date in 2017 Concur 2017 worksheet is located in column A. The number of rows in Concur 2017 is 63,146. I will be doing the same worksheets and formulas for 2018. I'll add the data on a monthly basis. I'm hoping to set up a macro once I get the formulas correct. I'll start a new thread to tackle the macros.


Let me know if I can provide anything else and I'm happy to do it. If the actual worksheets are needed, I'll put some fake data together. As you can see, the worksheets are fairly large. RIG Travel 2017 has around 42 columns and 2017 Concur has 23 and both have tens of thousands rows.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column Names[/TD]
[TD]RIG Travel 2017[/TD]
[TD]2017 Concur[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]T[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Crew ID[/TD]
[TD]AE[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Amount[/TD]
[TD]L[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Purpose[/TD]
[TD]OUTPUT AL[/TD]
[TD]K[/TD]
[/TR]
</tbody>[/TABLE]



<snip>

For reference, Column Headings:

RIG Travel 2017 2017 Concur [TABLE="width: 486"]
<tbody>[TR]
[TD]Date A T
[/TD]
[/TR]
[TR]
[TD]Crew ID B AE
[/TD]
[/TR]
[TR]
[TD]Transaction Amt C L
[/TD]
[/TR]
[TR]
[TD]Purpose OUTPUT (AL) K
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]