Vlookup / index match

Help Center Get Answers
1

Hi,

I have searched through for something similar to this, but cant find an answer. I can almost gaurantee there is one, but i just cant find it. So please feel free to point me in the right direction

I have a table set up with countries across the top (fields) and Posistions down the side (records). Is it possible for me to pull in the correct amount on another table, similar to a vlookup? My other table has a connection to choose the posistion. it also has another connection (to another table) to choose a site (of which we know the country). Can i use that info to pull through the correct amount?

example table:
image

Any help is really appreciated.

2

I’m not sure I really understand your question, or set-up, but maybe using conditional rules could help?

3

You should also check:
https://forums.knack.com/search?expanded=true&q=vlookup

4

Hello Chris,

In Knack, you need to create a table with 3 columns. Position, Country, and Amount, then you are able to filter by Position and Country.

Thanks,
Sunny Singla
ssingla1985@gmail.com
+919855089359

5

Hi Sunny,

Thank you for this. Would i need this to be an additional table? for example i dont want to present the user a list of the same posistions? ideally i would like them to choose the posistion and country and then the rate be pre selected. So i guess my question is should i have them choose the country, connected to one table about the location, choose the posistion from a second table just containing the posistions, and then have the rate selected from a third table set up as you mention, with the 3 columns (country / posistion / rate)?

Thanks again for your help

6

Yes, you can create a table with 3 columns.

And while displaying use the PIVOT table. Only 1 table is sufficient with 3 columns no need to create mutiple tables

Regards,
Sunny Singla

7

Hi Sunny,

Apologies i should have said. I plan to use the amount in a form for entering data into another table. The user will complete the form choosing the country, choosing the posistion and then have the amount already populated (thats my plan) .

Will that still work

8

Yes then we need to use another table and enter all data row by row for each country

9

Okay, so here is what i have.

I have 1 table with info about the site, one of the fields here is the country.

I have 1 table with positions in. This has the 3 columns (Position, Country, Rate)

I have a third table that i want to enter the data in to. I would like the user to choose the site, which in turn chooses the country (this i have done). I would then like the user to choose the posistion, but cant work out how to limit this choice to only those that are available in the country. If i can do this, i can then get the correct rate associated.

Is there a way to limit the choice so as to only display the positions that are for the country. Do i need another table with the countries listed, so as to be able to create a connection to the site, and a connection to the posistion? Currently these are just multiple choice.

Thanks again for your help thus far.

10

from playing around with this, i think i have to attach the position to the site, so that i can then restrict the page to ‘positions connect to the site’

However i am still stuck with the how to assign the site with the correct possitions, is this just going to be a manual thing? i have played around with the conditional rules on the site / posistion connection which seems to work, but if i add a new possition, it will require redoing all the conditional rules.

I think i must be missing something, but cant seem to figure this one out.