# Lookup Two Values

May 10, 2022 - by Bill Jelen

Problem: I have to lookup two values. I need to match both a company code and a cost center.

Strategy: There are three solutions to this problem: (a) Concatenated key, (b) `OFFSET`, or (c) `SUMIFS`. The concatenated key will only work if you are allowed to add a new column to the left of column C. The `SUMIFS` will only work if the value to be returned is numeric. The `OFFSET` will only work if all of the company codes are sorted together as shown above.

With a concatenated key, you will insert a new column before the Amounts in column C. You want to join column A, a unique separator, and column B. For example, `=A2&”-”&B2` would produce a key of 100-1010.

The separator text is optional. In real life, you might have two company/center combinations that would look the same once joined. Using a dash in between will prevent this ambiguity.

Once you have the concatenated key in the lookup table, you can join the key fields on-the-fly in your `VLOOKUP` formula:

``` =VLOOKUP(G2&”-”&G3,\$C\$2:\$D\$22,2,FALSE) ```

As mentioned earlier in this topic, this method only works if you are able to add the concatenated key to your data. It is fine to hide column C so no one see it, but you have to have the field there.

Alternate Strategy: If the value that you are trying to return is numeric, you can use `DSUM` or `SUMIFS`. For details, see Calculate Based on Multiple Conditions.

Alternate Strategy: Use the `OFFSET` function. Purists will argue that `OFFSET` is a volatile function and therefore slows down your calculation times. However, `OFFSET` will often solve problems where you need to reference a range that is moving or resizing.

`OFFSET` is used to point to a range. The location and size of the range is calculated as the formula is being calculated.

`OFFSET` allows five arguments. At least one of the four final arguments should be a formula that is calculated on the fly. When `OFFSET` is set up to return a range of cells, you will find yourself using `OFFSET` inside of another function such as `SUM`, or in this case, inside of `VLOOKUP`.

The syntax is `=OFFSET(Reference,Rows Down from There, Columns Right from There, Rows Tall, Columns Tall)`. For example, you could start with a reference of B1, move down N rows, move right 0 rows, make the range be 7 rows tall and 2 columns wide.

In the next figure, a `MATCH` function in F5 figures out where the lookup table for this company begins. The `COUNTIF` in F6 figures out how tall the lookup table should be. Both of these numbers will feed into an `OFFSET` function that is shown for illustration in F7. The actual formula is found in F9, where the `OFFSET` is used to describe the lookup table in the `VLOOKUP` formula.