How to vlookup with wild from lookup range

saudkazia

New Member
Joined
Mar 26, 2013
Messages
3
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
KB6130
KB5130
KB2125
KB1120
KB0100
JY025180
JY015140

<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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
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:

ABCDEFG
1ListMatchLookupValue
2KB58130KB6130
3KB52130KB5130
4KB69130KB2125
5KB05100KB1120
6KB68130KB0100
7KB04100JY025180
8JY025180JY015140

<tbody>
</tbody>
Sheet6

Array Formulas
CellFormula
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

New Member
Joined
Mar 26, 2013
Messages
3
Array Formulas
CellFormula
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
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! :biggrin:
 

Forum statistics

Threads
1,082,277
Messages
5,364,197
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top