VLOOKUP as range in multiple data source

[monish_chandra]

I have two data sources, the first one has a list of CPT codes, and the other one has a list of range and a category name for that range of values. I'm trying to write a workflow which will loop through each of the CPT codes in the first data source and search if it falls between any of the start and end points in the second data source and retrieve the Category value.

Data source 1

CPT code

10388

39000

99048

Data source 2

Start	End	Category
10000	20000	General Medicine
20001	40000	Anesthesia
90000	99999	Radiology

[Luke_C]

Hi @monish_chandra 

One approach you can use is to leverage the generate rows tool to make a record for each code within the ranges provided, then join those new rows against the codes you're looking up.

[fmvizcaino · Accepted solution]

Hi @monish_chandra ,

Here is a suggestion for you.

Best,

Fernando Vizcaino

[Austin004lasrado]

Hello @fmvizcaino and @Luke_C ,

I need similar solutions but i have Date range instead of numbers. below is the two files we have and i am trying to find the Place a person travelled in the particular month.

File 1			File 2
Headder 1	Month			Headder 1	Start month	End Month	Place
Mr.A	Mar-22			Mr.A	01-02-2022	28-02-2022	Mangalore
Mr.A	Apr-22			Mr.A	01-03-2022	30-04-2022	Bangalore
Mr.A	May-22			Mr.A	01-05-2022	30-05-2022	Hyderabad
Mr.B	Mar-22			Mr.B	01-03-2022	30-04-2022	Bangalore
Mr.B	May-22			Mr.B	01-05-2022	30-05-2022	Hyderabad
Mr.C	Mar-22			Mr.C	01-03-2022	31-03-2022	Bangalore
Mr.C	Apr-22			Mr.C	01-04-2022	30-04-2022	Hyderabad
Mr.C	May-22			Mr.C	01-05-2022	31-05-2022	Chennai
Mr.C	Feb-22			Mr.C	01-02-2022	28-02-2022	Bangalore

I am looking to get below result. Can you please help us here.

Headder 1	Month	Results
Mr.A	Mar-22	Bangalore
Mr.A	Apr-22	Bangalore
Mr.A	May-22	Hyderabad
Mr.B	Mar-22	Bangalore
Mr.B	May-22	Hyderabad
Mr.C	Mar-22	Bangalore
Mr.C	Apr-22	Hyderabad
Mr.C	May-22	Chennai
Mr.C	Feb-22	Bangalore