Need help with Formula Please - Nested Vlookup, LEN, SEARCH, etc.

[jberman]

I am using Excel 2013.

I downloaded a Bank of America Export that shows many transactions that have originated from employees using their Debit Cards. Each Debit card ends in a different set of 4 digits. I am trying to create a formula to FIND these 4 digits (they are usually after a bunch of XXXXs) and take those digits to look up which department the employee is in, and to put that department in the cell.

For Example, the export looks like this:

DELTA AIR 00623828305 DELTA.COM CA 24717057132871320458739 CKCD 3058 XXXXXXXXXXXX4466 XXXX XXXX XXXX 4466

4466 is the last four digits of the Debit Card. As you can see, the last four digits might show up 2x in the cell. This employee belongs to the department "Corporate".

I would like the results of the formula to show me "Corporate" in the cell.

I have a chart that looks something like this which is where the Formula should look for when pulling the Departments. Or if its easier to embed it in the formula, I could do that too.

4524	Bev Hills
4516	Bev Hills
4573	Corp
0421	Corp
8129	Madison
6248	Miami
4540	Wall Street
4482	Wholesale

<tbody>
</tbody>

Please help!! =] THANKS

 

[Eric W]

You could do something like this:

	A	B	C	D	E
1	DELTA AIR 00623828305 DELTA.COM CA 24717057132871320458739 CKCD 3058 XXXXXXXXXXXX4466 XXXX XXXX XXXX 4466	Corp		Last 4	Department
2				4524	Bev Hills
3				4516	Bev Hills
4				4573	Corp
5				421	Corp
6				8129	Madison
7				6248	Miami
8				4540	Wall Street
9				4482	Wholesale
10				4466	Corp

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula B1 =LOOKUP(2,1/FIND(D2:D10,A1),E2:E10) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

However, you would have to worry about false matches if the number is found somewhere else in the cell. Also, column D should be defined as Text so that you get the 0 in front of 421. You can make the list longer, but unused entries at the end should have something in them that will never match (QQQQQQQQ) to prevent false matches.