Matching Item Issue in Excel

majidsiddique · Oct 22, 2018

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.

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

majidsiddique · Oct 22, 2018

[TABLE="width: 374"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]
ITEM[/TD]
[TD]
Qty[/TD]
[TD][/TD]
[TD]
Master List[/TD]
[TD]
Qty[/TD]
[/TR]
[TR]
[TD]04122JM[/TD]
[TD]?[/TD]
[TD][/TD]
[TD]LD-44216U[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]04402SM[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-4296-8800[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]53280[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-4196-8800[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]05504SP[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-4092-5800[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]05508SP[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-4074-5800[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-2067L0000[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]76515[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-05508SP[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]76586[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-05504SP[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]786042[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-04402SM[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]7875440[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-04122JM[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]78860420[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-78860420[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]2090[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-7875440[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]2067L[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-786042[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]2292[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-7658600[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]4074-58[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-7651500[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]4092-58[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-7540200[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]4196-88[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-5328000[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]4296-88[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-22920000[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]44216U[/TD]
[TD][/TD]
[TD][/TD]
[TD]LD-20900000[/TD]
[TD]76[/TD]
[/TR]
</tbody>[/TABLE]

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 · Oct 22, 2018

How about PowerQuery (Get&Transform) ?

Code:

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ITEM[/td][td=bgcolor:#70AD47]Qty[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]04122JM[/td][td=bgcolor:#E2EFDA]

40

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]44216U[/td][td]

4

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]04402SM[/td][td=bgcolor:#E2EFDA]

36

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]4296-88[/td][td]

8

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]53280[/td][td=bgcolor:#E2EFDA]

68

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]4196-88[/td][td]

12

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]05504SP[/td][td=bgcolor:#E2EFDA]

28

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]05504SP[/td][td]

32

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]05508SP[/td][td=bgcolor:#E2EFDA]

28

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]05508SP[/td][td]

32

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]4092-58[/td][td=bgcolor:#E2EFDA]

16

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]4074-58[/td][td]

20

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]75402[/td][td=bgcolor:#E2EFDA]

64

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2067L[/td][td]

24

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]76515[/td][td=bgcolor:#E2EFDA]

60

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]76586[/td][td]

56

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]786042[/td][td=bgcolor:#E2EFDA]

52

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7875440[/td][td]

48

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]78860420[/td][td=bgcolor:#E2EFDA]

44

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2090[/td][td]

76

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]2292[/td][td=bgcolor:#E2EFDA]

72

[/td][/tr]
[/table]

Fluff · Oct 22, 2018

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

Peter_SSs · Oct 22, 2018

.. 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 · Oct 23, 2018

Peter_SSs said:
.. or with VLOOKUP as you originally tried

Thanks a lot sir, great vlookup concept,

majidsiddique · Oct 23, 2018

thanks Fluff, very excellent concept with *. thanks

Peter_SSs · Oct 23, 2018

majidsiddique said:
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.

Matching Item Issue in Excel

majidsiddique

Board Regular

Excel Facts

majidsiddique

Board Regular

sandy666

Banned - Rules violations

Fluff

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

majidsiddique

Board Regular

majidsiddique

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Forum statistics

Share this page

Matching Item Issue in Excel

Board Regular

Excel Facts

Board Regular

Banned - Rules violations

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock