How to vlookup with wild from lookup range

[saudkazia]

Hi suppose I have the following data as an example


[TABLE="width: 89"]
<colgroup><col></colgroup><tbody>[TR]
[TD]KB58[/TD]
[/TR]
[TR]
[TD]KB52[/TD]
[/TR]
[TR]
[TD]KB69[/TD]
[/TR]
[TR]
[TD]KB05[/TD]
[/TR]
[TR]
[TD]KB68[/TD]
[/TR]
[TR]
[TD]KB04[/TD]
[/TR]
[TR]
[TD]JY025


[/TD]
[/TR]
</tbody>[/TABLE]
and a lookup range as below
[TABLE="width: 237"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]KB6[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]KB5[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]KB2[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD]KB1[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]KB0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]JY025[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD]JY015[/TD]
[TD="align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]


basically I want to be able to add a cell/column in the first data set which will lookup the cell ie KB58 against the lookup range and give the amount if it contains the lookup value in the range. So in this case KB58 should be 130.

I would think this would be really easy to do but for some reason Excel tries to be difficult. I can do a vlookup for exact match so that JY025 and JY015 will be 180 and 140 respectively but the rest seem to give incorrect results (obviously).

I did a google to figure out what to do but didn't find a workable solution without involving convoluted formulas (which were not workable anyway for me) or vba (which I don't want) .

The excel file is an invoice sheet and the amounts in the lookup range would vary depending on customer or shipping etc. so lookup range has to be in a separate sheet

Hope I have been clear, please let me know what I need to give in order to make this easier to answer. Using Excel 2016 but the solution should work with anything over 2007.

 

[Eric W]

Fuzzy matching is actually a pretty complicated topic. Your particular question is not as difficult since you're looking at the left side only, but your lookup isn't sorted. So given your situation, you can try:

	A	B	C	D	E	F	G
List	Match	Lookup	Value
KB6
KB5
KB2
KB1
KB0
JY025
	JY015

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]KB58[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]130[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]KB52[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]130[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]KB69[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]125[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]KB05[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]120[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]KB68[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]KB04[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]180[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]JY025[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"][/TD]

[TD="align: right"]140[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=INDEX($F$2:$F$8,SMALL(IF(LEFT(A2,LEN($E$2:$E$8))=$E$2:$E$8,ROW($E$2:$E$8)-ROW($E$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[saudkazia]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]{=INDEX($F$2:$F$8,SMALL(IF(LEFT(A2,LEN($E$2:$E$8))=$E$2:$E$8,ROW($E$2:$E$8)-ROW($E$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]

Perfect!

I don't understand it really especially the last part Row($E$2)

And also why index

 

[Eric W]

It's an array formula, so it looks at a range of values.

LEN($E$2:$E$8) returns an array of the lengths of the values in E2:E8, or {3,3,3,3,3,5,5}.

LEFT(A2,{3,3,3,3,3,5,5}) returns the left part of A2 for that length, or {"KB5","KB5","KB5","KB5","KB5","KB58","KB58"}.

The IF now compares that list against E2:E8, giving results of {FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE}

For the TRUE values, it get the result of ROW($E$2:$E$8)-ROW($E$2)+1. The ROW($E$2:$E$8) returns {2,3,4,5,6,7,8}, but when you include the -ROW($E$2)+1, it turns to {1,2,3,4,5,6,7}, which is an offset into the range instead of a row. So the {FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE} becomes {FALSE,2,FALSE,FALSE,FALSE,FALSE,FALSE}.

The SMALL function finds the smallest value in the array, ignoring non-numeric values, or 2.

Then INDEX($F$2:$F$8,2) returns 130.

Hope this makes sense!