Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different dates.

If I work with vlookup or Index-match I got only the first price for a certain securities. So I am not able to find the price of a securities that match both the name of the securities and the date.

Could you advice if there is any way to overcome?

Answer:

Here are two possible solutions:

  • Create an excel 2007 table
  • Use an array formula

The easiest is the excel 2007 table.

Excel 2007 table

  1. Select the range

  2. Click "Insert" tab
  3. Click "Table"

  4. Click OK

  5. Click "black triangle" on Security header.
  6. Select one or more securities
  7. Click "black triangle" on Date header.
  8. Select one or more dates

Array formula

Array formula in F9:

=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER.

Alternative array formula in F9:

=INDEX(tbl, SMALL(IF(COUNTIFS($G$3, $B$3:$B$19, $G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER.

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Copy cell F9 and paste it to the right. Copy cell F9:H9 and paste down as far as needed.

Named ranges

tbl (B3:D19)

What is named ranges?

Explaining excel array formula in cell range F9:H10

Step 1 - Identify matching cell values

=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))

Let´s start with the bolded part of the array formula:

COUNTIF($G$3, $B$3:$B$19)

becomes

COUNTIF("SecurityB", {SecurityA, SecurityB, SecurityC, SecurityD, SecurityA, SecurityB, SecurityC, SecurityD, SecurityA, SecurityB, SecurityC, SecurityD, SecurityA, SecurityB, SecurityC, SecurityD, SecurityA})

and reurns this array: {0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0}

COUNTIF($G$4, $C$3:$C$19) returns this array: {0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19) becomes {0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0}*{0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0} and returns: {0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

Step 2 - Match array to row number

IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1)

becomes

IF({0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)

and returns:

{False, 2, False, False, False, 6, False, False, False, False, False, False, False, False, False, False, False}

Step 3 - Get table value using row number

Array formula in cell F9:

=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX(tbl, SMALL({False, 2, False, False, False, 6, False, False, False, False, False, False, False, False, False, False, False}, ROW(A1)), COLUMN(A1))

becomes

=INDEX(tbl, SMALL({False, 2, False, False, False, 6, False, False, False, False, False, False, False, False, False, False, False}, 1), COLUMN(A1))

becomes

=INDEX(tbl, 2, 1) and returns "Security B"

Download excel example file

Security.xlsx

(Excel2007  Workbook *.xlsx)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference) Returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

COUNTIF(range,criteria)

Counts the number of cells within a range that meet the given condition

MIN(number1,[number2])

Returns the smallest number in a set of values. Ignores logical values and text

COLUMN(reference) Returns the column number of a reference