3 Way Lookup By splitting the Cell value

[LizaRon]

[TABLE="class: outer_border, width: 50, align: left"]
<tbody>[TR]
[TD]Item1
[/TD]
[TD]Item2
[/TD]
[TD]Item3
[/TD]
[TD]N1
[/TD]
[TD]N2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]XX
[/TD]
[TD]MM
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]A
[/TD]
[TD]A
[/TD]
[TD]YY
[/TD]
[TD]NN
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]E
[/TD]
[TD]D
[/TD]
[TD]WW
[/TD]
[TD]OO
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]ZZ
[/TD]
[TD]PP
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: outer_border, width: 50, align: center"]
<tbody>[TR]
[TD]Item_Name
[/TD]
[TD]F_Name
[/TD]
[TD]L_Name
[/TD]
[/TR]
[TR]
[TD]A-B-C
[/TD]
[TD]ZZ
[/TD]
[TD]PP
[/TD]
[/TR]
[TR]
[TD]C-A-A
[/TD]
[TD]YY
[/TD]
[TD]NN
[/TD]
[/TR]
[TR]
[TD]B-C-C
[/TD]
[TD]XX
[/TD]
[TD]MM
[/TD]
[/TR]
[TR]
[TD]A-E-D
[/TD]
[TD]WW
[/TD]
[TD]OO
[/TD]
[/TR]
</tbody>[/TABLE]


I have a data which is the Table-1 in one sheet and in another sheet i have a data With Table - 2 only Item_Name
column has a data, I need to fill the F_Name and L_Name column data by spilting the values in Item_Name and
comparing with Item1, Item2 & Item3 if all the three values match return the N1 and N2 coulmn values to F_Name and L_Name for that particular Item Name

 

[mfexcel]

[TABLE="width: 375"]
<tbody>[TR]
[TD]Item1[/TD]
[TD]Item2[/TD]
[TD]Item3[/TD]
[TD]N1[/TD]
[TD]N2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]XX[/TD]
[TD]MM[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]YY[/TD]
[TD]NN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]E[/TD]
[TD]D[/TD]
[TD]WW[/TD]
[TD]OO[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]ZZ[/TD]
[TD]PP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item_Name[/TD]
[TD]F_Name[/TD]
[TD]L_Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A-B-C[/TD]
[TD]ZZ[/TD]
[TD]PP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C-A-A[/TD]
[TD]YY[/TD]
[TD]NN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B-C-C[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A-E-D[/TD]
[TD]WW[/TD]
[TD]OO[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

'where A1:E5 refers Table 1; A7:C11 refers to Table 2

In Cell B8
=VLOOKUP($A8,CHOOSE({1,2},$A$2:$A$5&"-"&$B$2:$B$5&"-"&$C$2:$C$5,D$2:D$5),2,FALSE)
CTRL SHIFT ENTER
'copy down and across

btw, you have no B-C-C in Table 1

Here's a post of related topic for your reference:
Perform VLOOKUP with 2 lookup values | wmfexcel

 

[LizaRon]

But i have those data in A8 already , i want a fromula for B8 and C8 cell , Initially i tried to split the data in A8 using find, mid , left functions and compared tat with A2:A5, B2:B5 and C2:C5 , but tat dint give me D and E values, the above logic is not working for me

 

[mfexcel]

I know you have data in A8, that's why the formula is intended in B8
Did you input by CTRL SHIFT ENTER?
It works fine at my side.

But i have those data in A8 already , i want a fromula for B8 and C8 cell , Initially i tried to split the data in A8 using find, mid , left functions and compared tat with A2:A5, B2:B5 and C2:C5 , but tat dint give me D and E values, the above logic is not working for me