Lookup Function or Excel formula for combination cells Values matched case

[Gunasekaran]

Dear Sir,

I am working on a VBA project. I have used VLOOKUP in Source Sheet, to get the Values from Master sheet.

My requirement is, ledger account start with 3, 4, 7, 8, 9 Series Numbers, Vlookup Function works directly from the master data. if its match this Ledger account series Number.

If Ledger account start with 5 & 6 series Number alone, then I will need to Combined "A" Column "Ledger accounts" with "B" Column "Dept codes", based on the combination, I will get it Values from Master data (refer the screen shot, Looking that BPC Description Values from Master Data )
I am looking any excel formula or VBA code.
I would appreciate your advice. Could you please?

 

[vletm]

Gunasekaran
One sample ...
to Master Data's cell D2 =A2&$B2 ... and copy down or use table
to Source Sheet's cell C2
=INDEX('Master Data'!C:C,MATCH(TEXT(IF(OR(LEFT(B2,1)="5",LEFT(B2,1)="6"),A2&B2,B2),"@"),'Master Data'!D:D,0))
... and copy down or use table

 

[Gunasekaran]

Hi Vletm, could you please add this formula in excel file and share it. because if i used i got #N/A error..

 

[Gunasekaran]

Hi Sir, Super i got it Answer.. Really thanks for your great Support I have another question. Shall I ask here or again raise new Post?

 

[vletm]

# 5 minutes ...
# Open a new Post

 

[Gunasekaran]

Ok Sir, Thanks a lot.. Very Great Support and help... I will post new one now.