CODEWITHSUNDEEP
excelvbadictionarydatabase-designexcel-formula
NotAnAmbiTurner
NotAnAmbiTurner

Reputation: 2743

Dictionary function built-in to excel?

This is potentially not a coding question, but it is a question about "programming" excel.

What I am wondering, essentially, is if there is any way to conditionally overlay values without using a massive amount of nested ifs, similar to how one access an object's attributes or a dictionaries' keys. If there isn't, I'd be interested to know what sort of VBA might accomplish the same effect (I don't know anything about coding in excel.

An apt analogy for what I'm trying to do might be a JOIN in SQL.

Anywho, for example, let's say I have table 1:

+--------+----------+----------+
| Type   | GST Tax? | PST Tax? |
+--------+----------+----------+
| ITEM_A | TRUE     | TRUE     |
+--------+----------+----------+
| ITEM_B | FALSE    | FALSE    | 
+--------+----------+----------+
| ITEM_C | TRUE     | TRUE     |
+--------+----------+----------+

And I have table 2:

+-------+--------+----------+----------+
| Date  | Type   | GST Tax? | PST Tax? |
+-------+--------+----------+----------+
| DATE1 | ITEM_A |          |          |
+-------+--------+----------+----------+
| DATE2 | ITEM_A |          |          |
+-------+--------+----------+----------+
| DATE3 | ITEM_C |          |          |
+-------+--------+----------+----------+

What I want to do is conditionally match the "Type" column on table 2, to the entries in table 1, and then based on the matches "pull" the GST / PST tax information from table 1 to table 2.

The other function I would like is that anything added to table 1 is "automatically" included in the function - so hardcoding nested if statements, for example, would not work.

This would be easy to do in code, but I don't know anything about coding for excel. Might be a good time to learn, but I would also be interested to know if anyone has an idea for a built-in excel function that might help this. I don't know where to start.

Thanks in advance!

Upvotes: 1

Views: 2016

Answers (1)

Kaiser
Kaiser

Reputation: 2005

As @cybernetic.nomad pointed out on the comments, you can use VLOOKUP() formula for that matter.

See example:

enter image description here

Formula on cell G3 of the example would be:

=VLOOKUP($F3,$A$3:$C$5,2,FALSE)

You can find more info of that formula on the link.

Note: If you want to look on more complicated cases you can use the combination of INDEX() and MATCH() formulas as well.

Upvotes: 1

Related Questions