How to vlookup with wild from lookup range

[saudkazia]

Hi suppose I have the following data as an example

KB58

KB52

KB69

KB05

KB68

KB04

JY025

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

and a lookup range as below

KB6	130
KB5	130
KB2	125
KB1	120
KB0	100
JY025	180
JY015	140

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

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
1	List	Match			Lookup	Value
2	KB58	130			KB6	130
3	KB52	130			KB5	130
4	KB69	130			KB2	125
5	KB05	100			KB1	120
6	KB68	130			KB0	100
7	KB04	100			JY025	180
8	JY025	180			JY015	140

<tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula B2 {=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))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[saudkazia]

Array Formulas Cell Formula B2 {=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))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

[/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!