VLOOKUP formula?
G

Hi NX world! I'll try to make myself as clear as possible...

I'm trying to recreate a vLookup formula [i.e. VLOOKUP(search-for, columns-range, return-column, close-match]), just like I did in Numbers. The closest I could get to it is with 

Norme.'6a0m0g - 6a3m30g'[PROVA = "DC"].'2'

where Norme is a table containing 33 sets of subtables named like '6a0m0g - 6a3m30g', each one containing 15 rows (selected with [PROVA= "DC"]) and 100+ columns ('2' can vary from 0 to 139).

So let's say I have someone scoring N in a test [Prova = ""DC"]. I need to extract that raw score ( '2', which should be my N value) ) from the prevoiously chosen subtable ('6a0m0g - 6a3m30g').

Hope that was clear. Otherwise I can provide further details.

Thanks for your help!

G

Ninox Profile

Hi G, 

We would like to understand your enquiry better. Could you please send us an e-mail to support@ninoxdb.de and explain a little bit more detailed what do you want to achieve and what do want to have as the result?

That would probably help us to give you a better support.

You are also invited to join our webinar every Tuesday. You can register for the webinar here: 

https://zoom.us/webinar/register/WN_zoSk2qyNSz6vLRFF1zlakg

Thank you very much. 

Best regards, Jörg

 

G

Ok, Thanks.

G

I explained my issue via email, but since it could help other users I provide a visual description here.

Schermata 2018-08-28 alle 19.57.45

Ninox Profile

Hi,

Did this ever get resolved? I'm looking for a similar function...a vlookup formula to pull values into a calculation. Thanks, Leah

G

Hi. It took ages but finally I came up with this:

let varA := 'your item';
let varB := 'your category';
let stdScore := 'your subtable'[Protocollo = varB];
stdScore[PG = varA].DC

Schermata 2019-04-15 alle 18.50.09

Ninox Profile

Thanks for the quick response! I haven't yet figured out how to convert my situation from your example, but it has given me a path. Thanks again!

G

No problem. Just ask if you need more, as it may help other as well.

Ninox Profile

Mine ended up being a bit easier than expected. I just needed a simple lookup from one table to the next. My mistake was not understanding that the "select" funciton returns an array so it didn't know how to return one value in the 'Hours' column.

Here's my example:

'Tree DBH' is an entry field in the Calculations table that I want to use for look up.

'Felling DBH' table contains 2 columns, DBH and Hours.

I want to look up "Tree DBH" and return the corresponding Hours field. 

let varTreeDBH := 'Tree DBH';
first(select 'Felling DBH' where DBH = varTreeDBH).Hours

Reply