Vlookup - PowerBI - Text range or list

[jaimemagana]

Hi there!

I'm trying to obtain the license plate for a number of vehicles, based on how do their license pates end.

Original lookup table:

Year

Jan

Feb

Mar

Apr

MAy

Jun

Jul

Aug

Sep

Oct

Nov

Dic

2022

2021

LML

LMX

LNN

LPD

LPW

LRP

LSF

LSP

LTD

LTP

LVD

LVV

2020

LFH

LFY

LGG

LGH

LGP

LHG

LJD

LJR

LKF

LKV

LLJ

LMC

2019

KTJ

KVB

KVX

KWT

KXR

KYN

KZK

KZY

LBN

LCG

LCY

LDR

2018

KHG

KHY

KJV

KKR

KLN

KMM

KNK

KPD

KPS

KRJ

KRZ

KSS

2017

JWN

JXF

JYB

JYT

JZP

KBM

KCH

KCT

KDH

KFC

KFW

KGN

(I did no copy the whole thing, for it is not necessary, that dates back to 2000, because it's not necessary)

When unpivoting the table to work with it, we obtains something like this (simplified for year 2021):

tbLookupPlateYear

Year	Month	3Letters	MonthNumber
2021	Dic	LVV	12
2021	Nov	LVD	11
2021	Oct	LTP	10
2021	Sep	LTD	09
2021	Ago	LSP	08
2021	Jul	LSF	07
2021	Jun	LRP	06
2021	May	LPW	05
2021	Abr	LPD	04
2021	Mar	LNN	03
2021	Feb	LMX	02
2021	Jan	LML	01

Now, lets obtain the plate date:

Example plate A:   E 1234 LSF

the date would be July 2021

Example plate B :  E 4321 LMM

The date would be between january and february 2021, so we state the lowest, january 2021

How can we do this in PowerBI? In Excel you would use vlookup, with no exact match, and it would work just fine. Not in PowerBI, that only outputs exact matches. 

Any ideas?

[v-kkf-msft · Accepted solution]

Hi @jaimemagana ,

You can convert the text to UniCode and then compare them.
1. First add a new column to table.

NumberCode = 
UNICODE ( 'Table'[3Letters] ) * 10000
    + UNICODE ( MID ( 'Table'[3Letters], 2, 1 ) ) * 100
    + UNICODE ( RIGHT ( 'Table'[3Letters] ) )

2. Then create the column in plate table.

Lookup = 
VAR Fir = UNICODE ( [3Letters] )
VAR Sec = UNICODE ( MID ( [3Letters], 2, 1 ) )
VAR Thir = UNICODE ( RIGHT ( [3Letters] ) )
VAR PlateUnicode = Fir * 10000 + Sec * 100 + Thir
VAR Tab =
    FILTER (
        'Table',
        'Table'[NumberCode]
            = MAXX ( FILTER ( 'Table', [NumberCode] <= PlateUnicode ), [NumberCode] )
    )
RETURN
    MAXX ( Tab, [Attribute] ) & " " & MAXX ( tab, [Year] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@jaimemagana , HI, would you share you excel vlookup solution for more clarity?

Regards,
John Basha Mattipati,
https://www.linkedin.com/in/john-basha-mattipati-06812980/