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

 

[Aladin Akyurek]

Not sure what to say as it seems you have difficulty adapting to your data layout the set up given in post #7 and provided in a workbook in post #8 ...

 

[Traveltime]

Not sure what to say as it seems you have difficulty adapting to your data layout the set up given in post #7 and provided in a workbook in post #8 ...

Thanks for your help! I was able to get Lookup to work. I had a rogue ")" included. I had miscounted when checking. I'll still try and make the index match work for the future.

I'm curious, you used / and * within the lookup formula. Is the formula dividing and multiplying?

 

[Aladin Akyurek]

Thanks for your help! I was able to get Lookup to work. I had a rogue ")" included. I had miscounted when checking. I'll still try and make the index match work for the future.

Great to hear this. Thanks for the update.

I'm curious, you used / and * within the lookup formula. Is the formula dividing and multiplying?

Yes. Here we have pairwise divisions and pairwise multiplications.

 

[Traveltime]

Great to hear this. Thanks for the update.



Yes. Here we have pairwise divisions and pairwise multiplications.

Huh, I have found that some and not all rows returns a solution. I thought it was the date issue, but a spot check shows the dates fall within two days for some of them. The employee ID and transaction amounts match. Hmmmm.

So I want to change the number of days to check within the date given to 4 from 2. When I change the 2 to 4, it doesn't work. Would you please let me know what I should change within the fomula to check within 4 days of the given date?

Thanks!

 

[Traveltime]

Both worksheets will have multiple rows with the same $ amount which I think may be causing the issue. For example, a four night hotel stay would show in multiple rows in each worksheet for the same $ amount. Say someone stays 3 nights at $165 per night, then there are three rows that contain $165 for each nightly charge in each worksheet. The output (business purpose) should be the same for each row, the output is #N/A right now. Note, the dates fall within range and the employee ID matches on both reports. Unique $ amounts for the date range and employee ID looks to return a good result and not #N/A.

 

[Aladin Akyurek]

Both worksheets will have multiple rows with the same $ amount which I think may be causing the issue. For example, a four night hotel stay would show in multiple rows in each worksheet for the same $ amount. Say someone stays 3 nights at $165 per night, then there are three rows that contain $165 for each nightly charge in each worksheet. The output (business purpose) should be the same for each row, the output is #N/A right now. Note, the dates fall within range and the employee ID matches on both reports. Unique $ amounts for the date range and employee ID looks to return a good result and not #N/A.

Care to post scaled-down samples along with the desired result?