3 Way Lookup By splitting the Cell value

LizaRon

New Member
Joined
May 13, 2014
Messages
3
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
 
Last edited:

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
Item1Item2Item3N1N2
BCDXXMM
CAAYYNN
AEDWWOO
ABCZZPP
Item_NameF_NameL_Name
A-B-CZZPP
C-A-AYYNN
B-C-C#N/A#N/A
A-E-DWWOO

<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
 
Last edited:

LizaRon

New Member
Joined
May 13, 2014
Messages
3
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
 
Last edited:

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,972
Messages
5,621,892
Members
415,864
Latest member
cybid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top