Matching Item Issue in Excel

[majidsiddique]

Hi,

i have a problem in excel sheet. below mention sheet in a excel sheet but here item length Qty is short as compare to Total Item List. i used Vlookup, match index formula but failed. lookup matched exact length of cell to another cell but here length is problem.

kindly send me solution for this issue.

ITEM	Qty		TOTAL ITEM	QTY
04122JM	?		LD-007042000	2
04402SM			LD-007046000	1
53280			LD-007048000	1
05504SP			LD-007645000	1
05508SP			LD-008631000	2
75402			LD-008633000	3
76515			LD-008634000	4
76586			LD-008953000	5
786042			LD-010387000	6
7875440			LD-041002JM	7
78860420			LD-04122JM	8
2090			LD-041590CS0	9
2067L			LD-04402SM	10
2292			LD-051102A00	11
4074-58			LD-051160000	12
4092-58			LD-053280000	13
4196-88			LD-05508SP00	14
4296-88			LD-061102A00	15
44216U			LD-070023000	16

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[majidsiddique]

ITEM	Qty		Master List	Qty
04122JM	?		LD-44216U	4
04402SM			LD-4296-8800	8
53280			LD-4196-8800	12
05504SP			LD-4092-5800	16
05508SP			LD-4074-5800	20
75402			LD-2067L0000	24
76515			LD-05508SP	28
76586			LD-05504SP	32
786042			LD-04402SM	36
7875440			LD-04122JM	40
78860420			LD-78860420	44
2090			LD-7875440	48
2067L			LD-786042	52
2292			LD-7658600	56
4074-58			LD-7651500	60
4092-58			LD-7540200	64
4196-88			LD-5328000	68
4296-88			LD-22920000	72
44216U			LD-20900000	76

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

i tried vlookup but failed:

=vlookup(left(a1,4),d1:e20,2,0) but this formula is reply

kindly help me to sovle this issue

 

[sandy666]

How about PowerQuery (Get&Transform) ?

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"ITEM - Copy"},Table2,{"Master List"},"Table2",JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Qty"}),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Removed Columns", "Table2", {"Qty"}, {"Qty"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Table2",{"ITEM - Copy"})
in
    #"Removed Columns1"[/SIZE]

ITEM	Qty
04122JM	40​
44216U	4​
04402SM	36​
4296-88	8​
53280	68​
4196-88	12​
05504SP	28​
05504SP	32​
05508SP	28​
05508SP	32​
4092-58	16​
4074-58	20​
75402	64​
2067L	24​
76515	60​
76586	56​
786042	52​
7875440	48​
78860420	44​
2090	76​
2292	72​

 

[Fluff]

Maybe
=INDEX(D$2:E$20,MATCH("*"&A2&"*",D$2:D$20,0),2)

 

[Peter_SSs]

.. or with VLOOKUP as you originally tried

Excel Workbook
	A	B	C	D	E
1	ITEM	Qty		TOTAL ITEM	QTY
2	04122JM	40		LD-44216U	4
3	04402SM	36		LD-4296-8800	8
4	53280	68		LD-4196-8800	12
5	05504SP	32		LD-4092-5800	16
6	05508SP	28		LD-4074-5800	20
7	75402	64		LD-2067L0000	24
8	76515	60		LD-05508SP	28
9	76586	56		LD-05504SP	32
10	786042	52		LD-04402SM	36
11	7875440	48		LD-04122JM	40
12	78860420	44		LD-78860420	44
13	2090	76		LD-7875440	48
14	2067L	24		LD-786042	52
15	2292	72		LD-7658600	56
16	4074-58	20		LD-7651500	60
17	4092-58	16		LD-7540200	64
18	4196-88	12		LD-5328000	68
19	4296-88	8		LD-22920000	72
20	44216U	4		LD-20900000	76
VLOOKUP (2)

 

[majidsiddique]

.. or with VLOOKUP as you originally tried

Thanks a lot sir, great vlookup concept,

 

[majidsiddique]

thanks Fluff, very excellent concept with *. thanks

 

[Peter_SSs]

Thanks a lot sir, great vlookup concept,

You're welcome.

BTW, I have pruned your long quote as they make the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.