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.

[TABLE="width: 105"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]4524
[/TD]
[TD="width: 76, bgcolor: transparent"]Bev Hills
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]4516
[/TD]
[TD="bgcolor: transparent"]Bev Hills
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]4573
[/TD]
[TD="bgcolor: transparent"]Corp
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]0421
[/TD]
[TD="bgcolor: transparent"]Corp
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]8129
[/TD]
[TD="bgcolor: transparent"]Madison
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]6248
[/TD]
[TD="bgcolor: transparent"]Miami
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]4540
[/TD]
[TD="bgcolor: transparent"]Wall Street
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]4482
[/TD]
[TD="bgcolor: transparent"]Wholesale
[/TD]
[/TR]
</tbody>[/TABLE]


Please help!! =] THANKS

 

[Eric W]

You could do something like this:

	A	B	C	D	E
DELTA AIR 00623828305 DELTA.COM CA 24717057132871320458739 CKCD 3058 XXXXXXXXXXXX4466 XXXX XXXX XXXX 4466	Corp	Last 4	Department
Bev Hills
Bev Hills
Corp
Corp
Madison
Miami
Wall Street
Wholesale
Corp

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4524[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4516[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4573[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]421[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8129[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6248[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4540[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4482[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4466[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LOOKUP(2,1/FIND(D2:D10,A1),E2:E10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.