Range lookup function?

Ask a question
1

Is there any way to do a range lookup? Something similar to VLookup in Excel?

for example, table A has a cell value 3 and table B has a lookup column with 3 rows, 1 cell has the value 2 and the other cell has the value 20, and last cell with the value 30. The lookup would approximate match on the cell with the value 2.

2

Normally I would say that a VLookup is best approximated by a Combine Tables step. But in this case, it won’t work, as it relies on an exact match, as opposed to an approximate match.

Would you be able to modify your lookup table so that instead of showing just 2, it shows a column with the range of values, one per row that relate back to 2? Something like this:

image

Which would allow an exact match to be performed.

3

Hi Brian,

Thanks, but can’t do that as it’ll take a lot of work to build that lookup that way.

Tried using Find/Overlap task and the approximation doesn’t work well. The approximation ended up matching .14 to 100.14 instead of to 1 as an example. Tried to match approximation with whole numbers, and 140 would match closer to 14 than 140

Also, if there are more math functions that would help, sound as rounding up and down by precision (5.4 round down to 5, or 5.4 round up to 6).

4

The Find/Overlap isn’t a good option, as it’s designed to match words approximately, not numbers.

You can indeed change the precision of a number, and it will round appropriately, by using the Format Numbers step. It’s not super obvious, but if you set the decimal place number, it will round. So if you want to round your decimals to whole numbers, set it to 0. Will rounding like that help?

5

Thanks Brian, I ended up creating a bunch of tasks to get this somewhat done. Basically did divisions, and then round and then multiply back (142.22 / 10 = 14.222. then round to 14 , then *10 , which gets 140), and had to do a bunch of if/then also so that anything below 10 doesn’t go thru that (to avoid 1.4/10 = .14 when then got rounded to 0). Once that was done, I did the lookup against simple ranges like 0,1,2,3…,10,20, etc…

Hope you guys add features for more math, better rounding up/rounding down and better numeric lookups and range lookups.

Thanks again.

2 Likes