vLookup equivalent between two tables

#1

I am really enjoying Coda so far, but seriously struggling with Lookups. I am trying to do the equivalent of a vLookup between two tables. I’ve ready everything I can find in forums, but haven’t cracked the code yet.

Screenshot below:
In the Purchasing.Category field, I would like a formula that will find a row specific value in Categories.Report based on a match between Purchasing.ProductlineDescription and Categories.Ops. I would like to do the same with Purchasing.SuperCategory based on Purchasing.ProductlineDescription and Categories.Ops.

With a vlookup formula, it would look something like this =vlookup(c2,categories,2,false), with categories being a named range.

I do a lot of this, so it will be very helpful. Thanks in advance.

#2

@Dave_Brown

Then you are going to learn to love Tables!! :smiley:

(open image in new browser tab to enlarge)

#3

Thanks, Ander. Superhelpful.

The purchasing table will be dumped from a database with thousands of lines and pasted into Coda. When I convert it to a lookup field from text, several lines will get an error message that they are not a valid row reference as new Purchasing.ProductlineDescription values will not have been added to the Categories table yet.

A few questions:

  1. Is there a quick way to identify all rows that have errors, i.e. filter them so they can be updated?

  2. Also, what if the Categories.Ops field is not a display column? That seems to break the formula. Is there a way to reference the field you want to match?

  3. Did you use ThisRow. in your super category formula so that the whole column to make it row specific?

1 Like
#4

@Dave_Brown

There are lots of ways to do that, depending on the exact details you’re working with. Experiment with the Filter() formula and see how far that gets you.

Two part answer:

  1. If it can be a display column, there’s a good chance it should be a display column, in its own table.
  2. If it must be in a non-display column, make the column a Select list column > Selectable options > formula (f icon) > =[categories].ops

I did not, but this scenario should work either way. Try it both ways and see what you learn.

#5

Thanks, Ander. I will try these recommendations.

1 Like