Question:

I have a table of 3 columns (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?

Table of Contents

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

Watch a video where I explain the formula

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:

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Count values, Excel

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 has a value (Column C) that […]

Comments(20) Filed in category: Excel, Filter records

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 […]

Comments(0) Filed in category: Excel, Filter records

How to extract unique distinct records:

Filter unique distinct row records

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

Comments(2) Filed in category: Excel, Unique distinct records

How to create a list of duplicate records:

Extract duplicate records

This article describes how to filter duplicate rows. The array formula in this article contains countifs, a function introduced in […]

Comments(0) Filed in category: Duplicate records, Excel

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:

COUNTIFS function

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

Comments(1) Filed in category: Excel

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}

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

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}

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

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"

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Download excel example file

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 […]

Comments(3) Filed in category: Excel, Unique distinct records

Filter unique distinct row records

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

Comments(2) Filed in category: Excel, Unique distinct records

 

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.

Comments(0) Filed in category: Excel, Excel table