3 Way Lookup By splitting the Cell value

[LizaRon]

Item1	Item2	Item3	N1	N2
B	C	D	XX	MM
C	A	A	YY	NN
A	E	D	WW	OO
A	B	C	ZZ	PP

<tbody>
</tbody>

Item_Name	F_Name	L_Name
A-B-C	ZZ	PP
C-A-A	YY	NN
B-C-C	XX	MM
A-E-D	WW	OO

<tbody>
</tbody>

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]

Item1	Item2	Item3	N1	N2
B	C	D	XX	MM
C	A	A	YY	NN
A	E	D	WW	OO
A	B	C	ZZ	PP
Item_Name	F_Name	L_Name
A-B-C	ZZ	PP
C-A-A	YY	NN
B-C-C	#N/A	#N/A
A-E-D	WW	OO

<tbody>
</tbody>

'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