Need to do a parent child lookup then reference a replacement value...

[gersh]

I have to translate a list of old client & matter numbers to new client and matter numbers.

Client and matter are related parent child, so for each client there can be many matters. And, matters can be the same for each client. For instance, below lists the old client and matter numbers along with the new client and matter numbers that correspond to each old client/matter combination (lawyers switching to a new law firm so the old client and matters numbers are changing to the format for the new firm)

Old Cli Old Mat New Cli New Mat
12345 001 56743 NH0010
12345 002 56743 NH0020
12378 001 56872 NH0010
12378 003 56872 BI00003

I need a formula that returns the correct new matter number for a given Old Cli and Old Mat. Getting a new client number for each old one is easy, thats a simple VLOOKUP. But to get the new matter I first need to look up the old client and matter, then reference the new corresponding matter. I am struggling with his. I think that maybe I can use and INDEX MATCH for this but not sure how it would be formatted. Please help!

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

Here is an example of what you could use.

Please note that the old Matter numbers have leading zeros. You can enter those several ways. The thing to remember is that it must be entered the same way everywhere you use it.

Excel 2013
	A	B	C	D
1	Old_Cli	Old_Mat	New_Cli	New_Mat
2	12345	1	56743	NH0010
3	12345	2	56743	NH0020
4	12378	1	56872	NH0010
5	12378	3	56872	BI00003
6
7	Old_Cli	Old_Mat		New_Mat
8	12345	1		NH0010
9	12345	2		NH0020
10	12378	1		NH0010
11	12378	3		BI00003
Sheet1

Cell Formulas
Range	Formula
D8	{=INDEX($D$2:$D$5,MATCH(1,($A$2:$A$5=A8)*($B$2:$B$5=B8),0),1)}
D9	{=INDEX($D$2:$D$5,MATCH(1,($A$2:$A$5=A9)*($B$2:$B$5=B9),0),1)}
D10	{=INDEX($D$2:$D$5,MATCH(1,($A$2:$A$5=A10)*($B$2:$B$5=B10),0),1)}
D11	{=INDEX($D$2:$D$5,MATCH(1,($A$2:$A$5=A11)*($B$2:$B$5=B11),0),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gersh]

I will give this a shot and let you know how it goes. Thank you very much for your help!

-Steve

 

[RickXL]

No problem.

Let me know if you get stuck.