Author: Oscar Cronquist Article last updated on November 15, 2018

Question:

I have a table of 3 columns (Security name, date, price) and I have to find the price of a security at a certain date in a table that contains 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 certain securities. So I am not able to find the price of securities that match both the name of the securities and the date.

Could you advise if there is any way to overcome?

### Array formula

The following image shows you a data set in cell range B2:D19, cell value G3 lets you match values in column B and cell G4 matches dates in column C. The formula returns matching records in cell range F9:H11 when both conditions are met.

Array formula in F9:

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

#### 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.

Recommended article:

How to enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

The following post shows you how to filter records using a single condition:
VLOOKUP - return multiple records

This post demonstrates how to filter records with two numerical conditions:

Extract all rows that contain a value between this and that

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

The following article shows you how to use an excel defined table to quickly filter values in a data set:

Quickly search a data set with many criteria

Mohsin Ali Raziq asks: I have problem, and o dont know how to solve it, i have data of almost […]

How to extract unique distinct records:

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

How to create a list of duplicate records:

Extract duplicate records

This article describes how to filter duplicate rows with the use of a formula.  It is, in fact, an array […]

Alternative array formula in F9:

=INDEX(\$B\$3:\$D\$19, SMALL(IF(COUNTIFS(\$G\$3, \$B\$3:\$B\$19, \$G\$4, \$C\$3:\$C\$19), ROW(\$B\$3:\$D\$19)-MIN(ROW(\$B\$3:\$D\$19))+1), ROW(A1)), COLUMN(A1))

The COUNTIFS function was introduced in Excel 2007:

How to use the COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

#### 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))

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}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

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}

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if 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"

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

Security.xlsx

(Excel2007  Workbook *.xlsx)

Recommended article:

Filter unique distinct records with a condition

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

### Excel defined 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

Recommended article:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.