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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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,102,184
Messages
5,485,245
Members
407,490
Latest member
leogaleleo84

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top