# 3 Way Lookup By splitting the Cell value

#### LizaRon

##### New Member
 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:

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### mfexcel

##### Well-known Member
 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

Last edited:

#### LizaRon

##### New Member
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
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

Replies
1
Views
259
Replies
1
Views
108
Replies
3
Views
133
Replies
7
Views
286
Replies
4
Views
233

1,181,921
Messages
5,932,805
Members
436,862
Latest member
OhioExcelGuy

### 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.

### Which adblocker are you using?

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

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