Vlookup with true case similar to Excel

[Shank]

Hi,

i am new to alteryx and I have two problems to be solved in it.

1. I am working on one of the report and i need to perform the Vlookup with true (which is used in excel) can some help on this?

below is an example. 

2. also, i need to roundup values how can i achieve that in alteryx?

ex: 2.342 to 2.3 and 4.677777 to 4.7 etc...

many thanks for your help

lookup Table:

Level	Type
0	L1
1.01	L2
1.5	L3
2.4	L4

desired Result need:

Level	Name	Dep	Type
0.2	A	A1	L1
0.3	B	A2	L1
0.2	C	A1	L1
0.1	D	A1	L1
0.2	E	A2	L1
0.1	F	A2	L1
0.4	G	L1	L1
0.4	H	L1	L1
0.3	I	P1	L1
0.6	J	SD	L1
1.3	K	SD	L2
1.6	L	FF	L3

Regards,

Shashank

[mceleavey]

Hi @Shank,

I'm not entirely clear on what you're trying to do there, but given "Level" is the only commonality, have you tried simply joining the data on "Level" and taking the results of the J output?

Let me know if that's what you need or if there's something I'm missing.

M.

[Shank]

Hi @mceleavey

I need to perfom a volookup with approximate match condition and not the extact match (Which we have in excel).

can we perform the same in alteryx?

Regards,

Shashank

[mceleavey · Accepted solution]

Hi @Shank,

Ok, I think what you're asking for is range matching. 

First, to round to a given number of decimal places, use the select tool, change the data type to "Fixed Decimal" and change the number after the decimal point to be the number of decimal places you want. for example, 19.6 to 19.1 if you only want one decimal place.

Second, to match to a range you can use a few methods. You can use a formula tool to say if it above x and below y then L1, for example, or you can generate rows.

I'll show you the second method.

I've opted to use a simple batch macro to create the range within each type, as follows:

All this is for is to create a range of numbers for each type incrementing by 0.01.

I've then embedded this in the workflow:

I then loaded in the output you were expecting, without the Level field. When I join it, I then get the appropriate level:

I think this is what you're after, let me know if there's anything else.

M.

[Shank]

This is really Great..!!!

Thank You for the help :)

[somolov]

Hello!

This macro seems to be what I need as well, but I can't seem to find the macro uploaded onto this forum. All I see is the workflow, which doesn't work without the macro. Am I missing something?

Thanks!