Query two fields and select answer from third field (If or vlookup?)

Question

I need to compare two fields in my data to a table and get the resulting fx value from table.

How the formula would read I would guess:

IF local_in_data = local_in_table AND 
  expense_in_data = expense_in_table THEN 
    take fx from table to data

data is:

expense  local  fx(formula)
EUR      USD    (formula) sh = 1.2
USD      USD    (formula) sh = 1.0

table is:

local   expense  fx
USD     EUR      1.2
USD     USD      1.0
EUR     USD      .08
USD     JPY      .009

How do I construct such a formula for my data fx column?

Answer 1

While a two column criteria lookup is very doable, you would only be picking up the first fx value. Since there should be only one set of matching pairs, a SUMIFS function is a very efficient alternative.

=SUMIFS(C:C, A:A, F3, B:B, E3)

C:C » column from which the result should be taken (if the following two criterias meet)
A:A » column to search in for your first criteria
F3 » cell which holds your first criteria
B:B » column to search in for your second criteria
E3 » cell which holds your second criteria

<sup>- Can be expanded up to 127 criterias
- If you drag down this formula F3 and E3 should be changed automatically</sup>