Vlookup Query

[marsh_win]

Hi Team,

Need support.

I have 2 sets of Data

One set of Data is as below

Code	Value
ZS-D*	50%​
FL-**	25%​
ZV-*	30%​
ZV-K*	25%​
FL	40%​

In another excel sheet i have a value ZS-DR. for this code i need excel to pick the value 50% from the above table.

How can this be possible using vookup.

Appreciate your support

 

[etaf]

a few ways to do that
does this work
=INDEX($B$2:$B$101,MATCH(1,COUNTIF(E2,"*"&A$2:$A$101&"*"),0))

Book9
	A	B	C	D	E	F
1	Code	Value
2	ZS-D*	50%			ZS-DL	50%
3	FL-**	25%			FL-123	25%
4	ZV-*	30%			FL	40%
5	ZV-K*	25%				#N/A
6	FL	40%
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX($B$2:$B$101,MATCH(1,COUNTIF(E2,"*"&A$2:$A$101&"*"),0))

 

[marsh_win]

a few ways to do that
does this work
=INDEX($B$2:$B$101,MATCH(1,COUNTIF(E2,"*"&A$2:$A$101&"*"),0))

Book9
	A	B	C	D	E	F
1	Code	Value
2	ZS-D*	50%			ZS-DL	50%
3	FL-**	25%			FL-123	25%
4	ZV-*	30%			FL	40%
5	ZV-K*	25%				#N/A
6	FL	40%
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX($B$2:$B$101,MATCH(1,COUNTIF(E2,"*"&A$2:$A$101&"*"),0))

Thanks etaf.. But one point.

for FL-123 the VLOOKUP should not revert with 25% as the main table array it can be only FL-** (Max of 2 digits after FL-

 

[etaf]

for FL-123 the VLOOKUP should not revert with 25% as the main table array it can be only FL-** (Max of 2 digits after FL-

ok, so will the value in the lookup ever be more than 2 digits ... if so then it will not occur
or do we need to test the entry and make sure it only has 2 digits after the entry

can you give a range of entries that are possible - as all you said in first post was to lookup

i have a value ZS-DR. for this code i need excel to pick the value 50% from the above table.

so i was just checking any possible other value

 

[marsh_win]

Than

ok, so will the value in the lookup ever be more than 2 digits ... if so then it will not occur
or do we need to test the entry and make sure it only has 2 digits after the entry

can you give a range of entries that are possible - as all you said in first post was to lookup


so i was just checking any possible other value

Thanks for taking time. Let me add more data. Assume below is the target Data

Code	Range
FL	5%​
FL-**	10%​
MG-C	15%​
MG-C*	20%​
MV-*	25%​
MV-K*	30%​
MV-O	35%​
NS-B	40%​
NS-C	45%​
NS-D	50%​
NS-D*	55%​
NV-*	60%​
NV-K*	65%​
RS-D	70%​
RS-D*	75%​
RV-*	80%​
RV-K*	85%​
ZG-T	90%​
ZG-T*	95%​
ZS-D	100%​
ZS-D*	105%​
ZV-*	110%​
ZV-K*	115%​

* indicate that a code can be added after the value. In that case for the below search results the excepted VLOOKUP needs to be

Vlookup	Expected vlookup results
FL-D	N/A
FL-DT	10%​
FL	5%​
MV-K	25%​
MV-KT	30%​
MV-KTO	N/A
NS-D	50%​
NS-DT	55%​
NS-OT	N/A

 

[etaf]

so is that all the possible combinations of lookup range *
2 , 4 & 5 characters length
and the * determine how many characters are allowed
so you wont have more than XX-**
And a - always appears with the * items

as you have for example
MG-C
and
MG-C*

Can be anything with MG-C - on its own or with 1 extra character
BUT if you only had
MG-C*
in the list - then MG-C would not be allowed , nor would MG-CAA


so FL-** in the lookup range
means when looked up value has to match the length of 2 characters after the -
so only 5 characters will be allowed

FL-12 - is OK
FL-1 is not
FL-123 is not
as the lookup range value is FL-** - so must have 2 characters after the - to be valid lookup

BUT as FL is in the lookup range , then only FL will match with that

not sure i have an answer for that combination - hopefully another member may have a solution - i'll have to have a think

 

[JEC]

I thought you asked for Power Query

First Source line is your codes table
Second line is the table which contains the percentages.

let
    SourceCodes = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SourcePerc = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    pList = SourcePerc[Value],
    perc = Table.AddColumn(SourceCodes, "Perc", each
      pList
      {
         List.PositionOf(SourcePerc[Code],
            Record.Field(
               List.Accumulate(SourcePerc[Code],[cnt=0,code=0], (s,c)=>
                    let
                        t = List.Count(Text.PositionOfAny(c, Text.ToList(_[Code]),Occurrence.All))  
                    in
                        if Text.Length(_[Code]) = Text.Length(c) and t = Text.Length(Text.Replace(c,"*",""))
                          then [cnt=t,code= c]
                        else s
                ),"code"
             )
          )
       }
    )
in
     perc

 

[Alex Blakenburg]

Give this a try:

20240314 Reverse Wild Card Match marsh_win.xlsx
	A	B	C	D	E	F
1	Code	Range			Vlookup	Return Value
2	FL	5%			FL-D	NA
3	FL-**	10%			FL-DT	10%
4	MG-C	15%			FL	5%
5	MG-C*	20%			MV-K	25%
6	MV-*	25%			MV-KT	30%
7	MV-K*	30%			MV-KTO	NA
8	MV-O	35%			NS-D	50%
9	NS-B	40%			NS-DT	55%
10	NS-C	45%			NS-OT	NA
11	NS-D	50%
12	NS-D*	55%
13	NV-*	60%
14	NV-K*	65%
15	RS-D	70%
16	RS-D*	75%
17	RV-*	80%
18	RV-K*	85%
19	ZG-T	90%
20	ZG-T*	95%
21	ZS-D	100%
22	ZS-D*	105%
23	ZV-*	110%
24	ZV-K*	115%
25
Sheet1

Cell Formulas
Range	Formula
F2:F10	F2	=IFNA(INDEX($B$1:$B$24,MATCH(1,MATCH(SUBSTITUTE($A$1:$A$24,"*","?"),E2,0),0),0),"NA")