## Match two criteria and return multiple records

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend the formula demonstrated below to include additional criteria.

If you have a scenario where you want to apply multiple conditions on a single column then read this article: Extract all rows from a range that meet criteria in one column [Array formula]

#### Table of Contents

## 1. Match two criteria and return multiple records [Array Formula]

The image above 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.

### 1.1 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 these 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 this?

Array formula in F9:

### 1.2 Watch a video where I explain the formula

### 1.3 How to create an array formula

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

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

### 1.4 Explaining excel array formula in cell range F9:H10

Use the "Evaluate Formula" tool to examine a formula in great detail. Go to tab "Formulas", press with the mouse on the "Evaluate Formula" button.

A dialog box opens, press the "Evaluate" button to see formula calculations step by step.

#### Step 1 - First condition

The COUNTIF function calculates the number of cells that is equal to a condition. We can use the COUNTIf function to match the condition to values in cell range B3:B19.

It returns an array containing as many values as there are cells in cell range B3:B19, the values can be 0 (zero) or 1. 1 indicates a match, we can use the array later on to match corresponding row numbers.

The position of a 0 (zero) or 1 in the array is important, the position matches the position in cell range B3:B19.

COUNTIF(*range, criteria*)

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 returns {0; 1; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0}.

#### Step 2 - Second condition

The second condition is a date specified in cell G4, the COUNTIF function counts the condition against dates in C3:C19.

COUNTIF($G$4, $C$3:$C$19)

becomes

COUNTIF(39815,{39814; 39815; 39816; 39817; 39814; 39815; 39816; 39817; 39818; 39819; 39820; 39818; 39819; 39820; 39818; 39819; 39820})

and returns {0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

#### Step 3 - Multiply arrays - AND logic

We need both conditions to be true in order to get the correct values. The asterisk character lets you multiply the array, this is possible because both arrays have the same size.

1 * 1 = 1

1 * 0 = 0

0 * 1 = 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 4 - Create number sequence

The ROW function calculates the row number of a cell reference. It can also return an array of row numbers if the reference is a cell range.

ROW(reference)

ROW($B$3:$D$19)

returns {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}.

#### Step 5 - Create number sequence from 1 to n

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value, lookup_array, [match_type]*)

MATCH(ROW($B$3:$D$19),ROW($B$3:$D$19))

becomes

MATCH({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19})

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}.

This array contains row numbers containing as many numbers as there are rows in cell range $B$3:$D$19.

#### Step 6 - Replace values in array with corresponding row number

Array value 1 is replaced with the corresponding row number. 0 (zero) is replaced with nothing FALSE.

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), MATCH(ROW($B$3:$D$19),ROW($B$3:$D$19)))

becomes

IF({0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, MATCH(ROW($B$3:$D$19),ROW($B$3:$D$19)))

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 7 - Extract k-th row number

The SMALL function returns the k-th smallest value from a group of numbers. It ignores text and boolean values.

SMALL(*array*, *k*)

SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), MATCH(ROW($B$3:$D$19),ROW($B$3:$D$19))), ROWS($A$1:A1))

becomes

SMALL({FALSE; 2; FALSE; FALSE; FALSE; 6; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, ROWS($A$1:A1))

The ROWS function returns the number of rows in a reference, we need the SMALL function to return a new row number in each cell, in order to do that I use the ROWS function and a reference that grows when you copy the formula and paste to cells below.

Reference $A$1:A1 has two parts, an absolute part $A$1 meaning it won't change when the formula is copied to cells below. The second part is a relative reference A1, it changes when the formula is copied.

SMALL({FALSE; 2; FALSE; FALSE; FALSE; 6; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, ROWS($A$1:A1))

becomes

SMALL({FALSE; 2; FALSE; FALSE; FALSE; 6; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, 1)

and returns 2. 2 is the smallest number in the array.

#### Step 8 - Get value

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(*array*, *[row_num]*, *[column_num], [area_num]*)

The COLUMNS function works just like the ROWS function except for columns instead, see the explanation above. This allows us to extract the entire row.

INDEX($B$3:$D$19, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), MATCH(ROW($B$3:$D$19),ROW($B$3:$D$19))), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$D$19, 1, 1)

and returns "SecurityB" in cell F9.

**1.5 Alternative array formula in F9 [Excel 2007]**

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

### 1.6 Excel file

#### Recommended articles

The following post shows you how to filter records using a single condition:

VLOOKUP - return multiple records

- Extract all rows that contain a value between this and that
- Quickly search a data set with many criteria
- Filter unique distinct records
- Extract duplicate records

## 2. Match two criteria and return multiple records [Excel 365]

Dynamic array formula in cell F9:

### 2.1 Explaining formula in cell F9

#### Step 1 - First condition

The equal sign is a logical operator that lets you compare value to value. In this case, a value to multiple values, the result is an array containing boolean values True or False.

G3=B3:B19

becomes

"SecurityB"={"SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"}

and returns

{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.

#### Step 2 - Second condition

The second condition identifies cells in cell range C3:C19 containing the date specified in cell G4.

G4=C3:C19

becomes

45659={45658; 45659; 45660; 45661; 45658; 45659; 45660; 45661; 45662; 45663; 45664; 45662; 45663; 45664; 45662; 45663; 45664}

and returns

{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

#### Step 3 - AND logic

Both conditions must be met, the asterisk character allows you to multiply the arrays meaning AND logic is applied.

TRUE * TRUE = TRUE (1)

TRUE * FALSE = FALSE (0)

FALSE * TRUE = FALSE (0)

FALSE * FALSE = FALSE (0)

When boolean values are multiplied their numerical equivalents are returned. TRUE = 1 and FALSE = 0 (zero).

(G3=B3:B19)*(G4=C3:C19)

becomes

{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} * {FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

and returns

{0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

#### Step 4 - Extract records

The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category and is only available to Excel 365 subscribers.

FILTER(*array, include, [if_empty]*)

FILTER(B3:D19, (G3=B3:B19)*(G4=C3:C19))

becomes

FILTER(B3:D19, {0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns

{"SecurityB", 45659, 1.4; "SecurityB", 45659, 70.6}.

### 2.2 Excel file

## 3. Match two criteria and return multiple records [Excel defined Table]

- Select the range

- Press with left mouse button on "Insert" tab
- Press with left mouse button on "Table"

- Press with left mouse button on OK

- Press with left mouse button on black arrow next to header "Security".
- Select the items you want to filter.
- Press with left mouse button on black arrow next to header "Date".
- Make sure only 1-2-2009 is selected.

The image above shows both conditions applied to the Excel Table.

#### Recommended articles

## 4. Match two criteria and return multiple records [Advanced Filter]

The image above demonstrates a filter applied to a data set using Excel's Advanced Filter feature. Here is how to create that filter:

- Copy headers and paste to cells below or above the dataset.
Note, the filter values may become hidden if you place them next to the dataset.
- Type the conditions below each header accordingly.
- Select the dataset.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Advanced" button, a dialog box appears.

- Press with left mouse button on radio button "Filter the list, in place".
- Press with left mouse button on "Criteria range:" field and select cell range B2:D3, see image above.
- Press with left mouse button on OK button.

The image above shows records filtered on items based on condition in B3 and dates based on condition in C3. If both conditions match on the same row the record/row appears in the filtered list.

Put the conditions on a row each in order to apply OR-logic instead of AND-logic between conditions, see image below.

#### Recommended articles

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Search for a text string in a data set and return multiple records

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

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

How to extract rows containing digits [Formula]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

Extract records between two dates

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Extract records containing negative numbers

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Filter records based on a date range and a text string

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

### 165 Responses to “Match two criteria and return multiple records”

### Leave a Reply to Liudas

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Oscar, your blogs do stretch make my Excel understanding. I have learnt a lot, especially in the usage of these powerful Array formulas. Thanks

In this case its your results would be great for producing a filter list of the criteria mentioned. However if you looking for just a price based on the criteria mentioned, this formula would be more simpler.

=INDEX($D$3:$D$19,MATCH($G$3&$G$4,$B$3:$B$19&$C$3:$C$19,0))

But I guess, the above formula does not work for multiple items of the same criteria....... sorry, I long way to go for me:)

Problem description (simplified of course):

I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each TAB is a separate month (first is Jan, 2nd is Feb, etc.). I have 12 tabs (12 worksheets) in workbook. Each TAB, a single month, has a list of ID numbers. Some IDs may repeat on different worksheets, that is, some may be in multiple months and some may be in just two or three months or just one month. An ID number will shown only once in a month for a single task (duty). Abbreviated example is below.

Is it possible to combine the data, by function, or formula, or VBasic) to a 13th worksheet automatically and:

1. Show a list of all ID numbers in order (without repeating).

2. Show Jan data in col B, Feb data in col C, etc., and some columns will be blank because the ID had no assignment that month, and will not be on the worksheet for that month.

Is there a formula, or function, or does it have to be done in VBasic? (Is it even possible?)

I have the workbook with 12 tabs in it, and now have to manually put the ID columns side by side and copy and slide down one side on the other to get them to match, and repeat the process 12 times to get the yearly data on one worksheet.

Ex:

For Jan:

ID Duty Asgn.

01 C

05 F

09 D

15 X

23 P

For Feb:

ID Duty Asgn.

02 M

05 Q

08 A

12 R

20 W

Combing Jan and Feb would be:

ID Duty Asgn.

01 C

02 M

05 F Q

08 A

09 D

12 R

15 X

20 W

23 P

This would be repeated for each month to build all 12 col months.

Very Respectfully,

Dave Bonar

(504) 697-2395

Dear sir

If you send the excel file, then I'll understand easily and give the better formula. Based on your requirements

Thankyou

Dave Bonar,

Yes, I believe this can be automated using vba. Some of the actions required can also be automated using excel formulas.

Very interesting questions! I´ll try to answer your questions as soon as possible here on my website.

/Oscar

Dave Bonar,

See this post: https://www.get-digital-help.com/2010/02/28/combine-data-from-multiple-sheets-in-excel/

/Oscar

Hello, Oscar,

First of all id like to thank you for your blog. I have found many very usefull tips and answers, but still i have one problem that i cant solve by my self. So im asking for your help.

Here is the problem:

i have a data table with 2 columns:

A B

2.93 12.8

2.94 12.2

3 8.38

3.03 6.76

3.04 5.33

3.06 6.36

Lets say i have a cell with number 3. I need to find a number in column A that has a number >= than 3, but also has the smallest number in column B.

(with my cell = 3 it would be 3.04 from A and 5.33 from B)

Simple vlookup gives me first >= number, but in most cases in column B is not the smalest number.

I hope you can help me,

Best regards,

Liudas

Liudas,

see this post: https://www.get-digital-help.com/2010/03/24/lookup-using-two-criteria-in-excel/

hi oscar,

1) am interested to know what is the array formula for only 1 criteria (for example above, Security, only?

2) how to remove/hide the #num! ?

thanks

David,

1) See this post: https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

2) Excel 2007: IFERROR(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));"")

Oscar,

Great Work on this one. This fixed one of my remaining bugs in my spreadsheet. Using the example above, how would you sort the results by 'Price' within the formula?

Thanks

Oscar,

Great Work on this one. This fixed one of my remaining bugs in my spreadsheet. Using the example above, how would you sort the results by 'Price' within the formula?

Thanks

Tom,

Try this array formula in cell F9:

=INDEX(tbl, MATCH(SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), $D$3:$D$19), ROW(A1)), $D$3:$D$19, 0), COLUMN(A1)) + CTRL + SHIFT + ENTER

Copy cell F9 and paste it to H9.

Copy cell range F9:H9 and paste it down as far as needed.

Oscar,

This is close to what I need. In my spreadsheet I do not have the Date to sort by. When I remove the *COUNTIF($G$4,$C$3:$C$19) portion it shows all of the particular Securities. So far so good. Now when I have two securities with the same price on different days it is not soring corectly(notice the date cells in the results). If all of the prices are different it works fine.

IFERROR(INDEX(tbl, MATCH(SMALL(IF(COUNTIF($G$3, $B$3:$B$19), $D$3:$D$19), ROW(A1)), $D$3:$D$19, 0), COLUMN(A1)), "")

Thanks

=INDEX(tbl, MATCH(SMALL(IF(COUNTIF($G$3, $B$3:$B$19), COUNTIF($D$3:$D$19, "<"&$D$3:$D$19)+ROW($B$3:$B$19)/1048576), ROW(A1)), COUNTIF($D$3:$D$19, "<"&$D$3:$D$19)+ROW($B$3:$B$19)/1048576, 0), COLUMN(A1))+ CTRL + SHIFT + ENTER

Copy cell F9 and paste it to H9.

Copy cell range F9:H9 and paste it down as far as needed.

Oscar,

Thanks a bunch. I was able to adapt this to my sheet and got it to work perfectly. Your knowledge is a great asset to others.

While I was able to adapt it, I am not quite sure what it was doing. Can you provide some insight on what this bit is doing:

COUNTIF($D$3:$D$19, "<"&$D$3:$D$19)+ROW($B$3:$B$19)/1048576)

Again thanks for your help.

Tom

Tom,

COUNTIF($D$3:$D$19, "<"&$D$3:$D$19) creates an array containing numbers. The numbers indicate the rank each cell value would have if they were sorted from A to Z. Now if there are two identical cell values the array formula (COUNTIF($D$3:$D$19, "<"&$D$3:$D$19) creates two identical rank numbers. That is why you got the wrong date when you had two identical securities with the same price. To create unique rank numbers I added this to the formula: ROW($B$3:$B$19)/1048576

Awsome! Thanks for the explination.

Oscar,

I am on to the next part of my project now.

Is there a way to combine all of the results into a single cell like with a concatenation with out the formula being extremly large and not containing cells with no values or the seperation characters.

In the above array Formula sample Cell H9 would result in:

$1,40, $70,60

I need to do the whole array and the concatenation in a single cell.

I have a sample spreadsheet of exactly what I trying to accomplish, but I do not know how to get it to you.

Thanks,

Tom

Tom,

As far as I know, concatenate can´t be used in array formulas.

Read about: String Concatenation

Hi Oscar,

Are you able to do this formula but instead of using a specific date, use a greater than date?

Arielle,

=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 copied right as far as needed and then copied down as far as needed.

Dear Oscar,

Thank you for this blog. I applied the formula as specified below and it worked well for me.

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

How would I weak the formula if I want to still match the 2 criterias of your example (i.e. Security and Date) and in addition sort on the price (e.g. increasing prices)?

Thanks,

Boris

Sheet1

A B C D

8 Country Europe

9 Lights 100

10 Type A 200

11

12 Country USA

13 Fuel 40

14 Diesel 200

15

16 Europe Lights Type A 100

17 USA Fuel Diesel 40

Oscar,is there a way to organize this the information into a database format like row 16 onwards,

It picks up all non blanks between the countries putting each line into a separate column.

Ignore the numbers after type a and diesel in the first half.

Boris,

How would I weak the formula if I want to still match the 2 criterias of your example (i.e. Security and Date) and in addition sort on the price (e.g. increasing prices)?In your example, I think an array formula would be too complicated. I suggest you use an excel table.

Sean,

Formula in cell A19:Formula in cell B19:Get the Excel 2007 file *.xlsxorganize-information.xlsx

Oscar,

Thanks. This is very tricky. The row called Country is the dividing line between each section. I am looking to pick up all the non-blank rows between each section. Move everything from column A besides country over to column B. Ignore the amounts in that is in now in column C. My table was slightly wrong. The amount is in the row below country. So the table looks like this.

Country USA

Lights 100

Type A

CFL

Country Europe

Diesel 50.00

Fuel

USA Lights Type A CFL

Europe Diesel Fuel

Is there any to paste screenshots here?

Sean,

If there is not the same number of rows between sections and country is the dividing line, I think vba is the tool for this task.

Sean,

Read this post: Excel udf: Reorganize data

Dear Mr. Oscar,

Here is my problem.

am having col1, col2, col3 and many data below that.

now i want to create 3 data validation.

Source for the First data validation is all col1.

Source for second data validation is col2 which is match with col1.

Source for third data validation is col3 which is match with col1 and col2.

hope this is clear. please help me

[email protected]

how to retrieve a cell value based on other two cell value by using formula (not using VBA)

b e 1

b f 2

d g 1

d h 4

if i enter "b" and "e" means i should get 1

if i enter "b" and "f" means i should get 2

thanks in advance

[email protected]

I a column with over 400 entries. Most of them are 0s. I would like to list the 5 smallest numbers excluding 0s. What is the best possible formula. Thanks

Muhammad Saleem,

read this post: List five smallest numbers, excluding zeros.

Please can someone help me with this:

I need a function (no macros) that will look at D2, go to column B and display everything in column A thats in column B in ascending order by sorting column C. exampl is below

Name group Invested lookup Answer

First Back $5.00 Back Third

Second Back $6.00 Second

Third Back $7.00 First

Forth Front $10.00

Fifth Side $11.00

Sixth SideA $12.00

A B C D E

Name group Invested lookup Answer

First Back $5.00 Back Third

Second Back $6.00 Second

Third Back $7.00 First

Forth Front $10.00

Fifth Side $11.00

Sixth SideA $12.00

Hi,

In your "Security, Date, Price" scenario I want to match only Security role and return multiple rows. I don't want to match Date. Please help

This is tremendously useful... but what if I need to add additional nested criteria, e.g., if ((A and B) or (c))? The use case I have is that I want to create the list based on the following:

Region: Northeast, Mid-Atlantic, Southeast, etc...

Number: Must be greater than the specified number

Flag 1: if it contains an 'x', add to the list

Flag 2: if it contains an 'x', remove from list

I tried nesting the array calculations as follows:

IF((COUNTIF($I$7,'Customer Stats'!$C$2:$C$206)*COUNTIF($B$3,"<"&'Customer Stats'!$D$2:$D$206)) + COUNTIF($B$4, 'Customer Stats'!$J$2:$J$206))

Where $I$7 contains the Region, $B$3 contains the number above which a record must be to qualify, and $B$4 contains an 'x' if we want to match corresponding records in $J$2:$J$206... but apparently I can't nest these array calculations.

If I add it as another * array, I can get the flagged records to show up in the list, but then any record that shows up must be flagged.

Any ideas?

Thanks,

-Adam

srikanth,

Hi,In your "Security, Date, Price" scenario I want to match only Security role and return multiple rows. I don't want to match Date. Please help

This formula should do it:

You could also use the formula in this post:

How to return multiple values using vlookup in excel

Awesome, but I have one question?

The above formula matches only security role and returning multiple rows, the output is perfectly fine.

what I want is exclude the "security" column itself in output.

Thanks for your valuable time.

Qadeer

Adam,

Check out the attached file:

Adam.xls

Hi Oscar -

Thanks for the sample... so very close, but I need to restrict the list to only the specified region -- even if flag 1 matches. I will play with it a bit and see what I can do, but if you have a quick solution, do let me know!

Thanks again,

-Adam

Got it, I think... this seems to work, but still testing:

=INDEX($A$5:$C$19, SMALL( IF((COUNTIF($A$2,$A$5:$A$19)*($B$5:$B$19>$B$2))+(COUNTIF($A$2,$A$5:$A$19)*ISNUMBER(SEARCH($C$2,$C$5:$C$19))), MATCH(ROW($A$5:$A$19),ROW($A$5:$A$19)), ""), ROW(A1)), COLUMN(A1))

Adam,

Open attached file:

Adam1.xls

Also close... but that sheet requires the flag to match as well as the region to match... in my code above, I basically * the array, and then add it to a second *'d array, which seems to do the trick. I also subtracted a 3rd *'d array for the list items that we do not want to include no matter what (similar to the flag1 in this example). Only bug seems to be with those list items that have the exclude flag2 set but do not meet the > number requirement, but I can live with that for now... Here is my final code (please excuse the Name references which I added for future maintainability):

=IFERROR(INDEX(AllDataNoHeadings,SMALL(IF((COUNTIF($A$7, AllRegions)*(AllRacks>=$B$3))+(COUNTIF($A$7, AllRegions)*ISNUMBER(SEARCH($N$2,Strategic)))-(COUNTIF($A$7,AllRegions)*ISNUMBER(SEARCH($N$3,NonStrategic))),MATCH(ROW(AllCustomers), ROW(AllCustomers)),""),ROW(A1)),COLUMN(A1)), "")

Thanks again for this excellent website... really helped a lot!

-Adam

This formulas works better then the one I am currently using, it only does one criteria. However I am using the formula on a separate worksheet with my data in a sheet called CSAT Data.

The formula I am using, built from the one in the example, referring to the CSAT Data worksheet will result in a n/a message.

How do we reference the name on a worksheet in the original formula?

Any help would be appreciated!

Ray,

How do we reference the name on a worksheet in the original formula?Example:

'CSAT Data'!$A$1

Try this syntax (Array formula so remember to C.S.E!)

{=MATCH(B57&C57,B2:B51&C2:C51,0)} for Excel 2007 or higher:

means return the first row number where a value in Col B matches search value in B57 and a a value in Col C matches search value in C57 ON THE SAME ROW!!

Mike,

MATCH(B57&C57,B2:B51&C2:C51,0)} doesn´t work if there are duplicate rows (B2:C51) but with different prices. See row 3 and 7. MATCH(B57&C57,B2:B51&C2:C51,0) returns only the first value.

Thanks for commenting!

Hi!!

This blog is awesome.

How do you return multiple matches in above example, i.e matching 2 criteria by using "SUMPRODUCT" function?

I am trying to match 5 digit numbers to 10 digit numbers in two different excel sheets.

For Example:-

Sheet1:-00085

Sheet2:-9310008522

what function should i use to match these values as data is huge and 5 digit values are also not fixed.

Ramki,

I dont know how.

Why do you want to use sumproduct?

Hi!! Oscar,

Thanks for the reply.

To return multiple values, I felt comfortable with sum product usage.

But I am able to get only Max Value of an array with sum product.

If you help me to get all related multiple values, I would be grateful.

Guarav,

You can use the search function.

Get the example file:

Guarav.xlsx

hello Oscar, i am building a spreadshet for tracking calls for my local fire depatrment. i have column "a" as incident number. the incident number is a one timme yearly number usage. column "c" is apparatus name and there are 1 of 8 possible names may be used in this cell. column "h" has the formula to give me the time spent on scene. i am needing help getting sheet 2 to tag the time spent on a call per apparatus. sheet 2 is names of personnel on scene. i want to put the time on scene according to what apparatus they were on for each incident.

ecample:

column "a" newest entry is #10

column "c" is "bt1" or "bt2" or "e1" or "e3" or "e4" or "e5" or "pov" or "stby"

there often will be multiple rows with the same incident# in column "a" but differant apparatus in column "c".

Column "h" will have on scene time calculated by "=f5-d5"(for that row)

i need to tag the on sceen time from sheet 1 column "h" to the corrisponding incident number column "a" according to the apparatus column "c".

Last Total

Enroute Arrival Clear Response Incident

Incident # Date Apparatus Time Time Time Time Time

1 03/01/12 bt2 8:18 8:27 18:45 0:09:00 10:27:00

2 03/25/12 bt2 8:20 8:23 17:45 0:03:00 9:25:00

e1 17:05 17:10 17:45 0:05:00 0:40:00

e3 12:33 12:38 17:45 0:05:00 5:12:00

3 03/26/12 e4 7:45 8:08 10:22 0:23:00 2:37:00

4 03/26/12 bt2 11:14 11:16 11:29 0:02:00 0:15:00

5 03/27/12 pov 13:10 13:20 18:36 0:10:00 5:26:00

stby 13:15 13:20 18:36 0:05:00 5:21:00

bt1 13:15 13:20 18:36 0:05:00 5:21:00

bt2 13:16 13:21 18:36 0:05:00 5:20:00

6 03/28/12 e1 8:18 8:27 18:45 0:09:00 10:27:00

e3 8:20 8:30 18:45 0:10:00 10:25:00

7 03/28/12 bt1 8:20 8:23 17:45 0:03:00 9:25:00

e5 9:00 9:03 17:45 0:03:00 8:45:00

8 03/28/12 bt2 9:20 9:22 9:59 0:02:00 0:39:00

9 03/29/12 e1 17:45 17:50 18:00 0:05:00 0:15:00

Bill Truax,

read this post:

Tracking calls in excel

Hi Oscar,

Just come across your blog - very useful, and I intend to get stuck into it. I've a quick question on the use of Sum If and And that I can't find on your site. I have the following spreadsheet example

Jan Ann 5

Jan Mike 6

Jan Pete 7

Feb Ann 8

Feb Mike 9

Feb Pete 10

March Ann 11

March Mike 12

March Pete 13

I'm looking for a function where I can search for Feb, and Mike and return the number 9

I feel this is a bit lowbrow for your site - but I'm stuck on it - sorry

Thanks

Ray

For the sake of clarity - they were supposed to show as separate columns - so the month is in column A, Name in Column B, and number in column C

Found the solution within the blog - using SUMIFS - tks for the content

Hi Oscar,

I have been looking at your example today and scratching my head about why it doesn't work in my version of excel. Finally, I've come to the conclusion that this must have something to do with the fact that I looked at your workbook in my German Language Version of Excel.

Somehow the formulas, especially SMALL, don't seem to update their relative reference when I do the Array.

Just for the sake of knowing... you don't perform anything other then a CTRL+ALT+RETURN when creating the Array, right?

Cheers,

Lukas

Hi Oscar,

Sorry for the earlier post. Feel free to delete it. I've just learned that I simply need to create the array in a single cell and then copy it down.

Thanks for the article! Very interesting stuff!

Lukas

Hi Oscar,

How to show the result of the array formula on new worksheet in the same file?

Hi Oscar,

Please ignore my previous post. I wanted to ask about two possibilities about the array formula explained for Security.xlsx file.

1) How to display the result on new worksheet in the same file?

2) I want to search records for the month of January in your example file?

I want to know the solution ASAP.

Look forward to your help in this matter.

Cheers,

M. Nadeem Bhatti.

Hi Oscar,

Any chance this could be modified to generate the resulting list across multiple tabs, 23 rows per tab?

So the formulas in the second tab would ignore the first 23 matching results, and the formulas in the third tab would ignore the first 46 results, etc.

Thanks,

...using the array, that is.

Pivot Tables and macros are not an option for this particular workbook.

thanks

Hi,

I have a model that has three workbooks. Workbook 1 and 2 contains different data sets – there are only two columns in worksheet 1 that can be matched to worksheet 2. Worksheet 3 is my summary page where i am displaying results.

I do require the data be tested for conditions – that is, I am only interested in a row in Worksheet 2 that has two specific columns that match two specific columns in a row in Worksheet 1. I want to view a certain cell of each of these rows where the condition has been proved.

This problems is similar to the one posted at the inception of this thread, however i require greater flexibility in criteria. Essentially i require for each row in worksheet 2 to be scanned against worksheet 1 and, when the above criteria is satisfied, display a cell from that matching row.

Please see below for a portion of the data. Imagine that each of the tables are in different worksheets.

I want certain cells in worksheet 2 from rows where: ‘tenant name’ in worksheet 2 matches ‘building/tenant’ in worksheet 1 and where ‘premises’ in worksheet 2 matches ‘suite id’ in worksheet 1. Curlington Legal Consulting is an example of such a match.

This is required as i need data from one worksheet, however the other contains critical search data.

I also require an additional screening - that is, i do not require any data from worksheet 1 (even if both previous fields have been met) if the expiry is greater than six months away.

Can anyone propose some formulas that could retrieve what i need?

All help is greatly appreciated. It has many people at my firm stumped!

Tony,

I am not sure I am following but I gave it a try:

Tony.xlsx

Beth,

Two or three tabs maximum using array formulas, I think:

Vlookup across multiple sheets in excel

Muhammad Nadeem Bhatti,

1) How to display the result on new worksheet in the same file?Adjust cell references. Example:

2) I want to search records for the month of January in your example file?Thanks Oscar,

how do i upload a file to show you specifically what i mean?

Hello Oscar,

I want to search a first vertical range, and then a second vertial range, and then referce the cell in the third column for which the range searches are true.

e.g. I search range A1:A10 looking for the value 1, and find it in A1 and A2. Now I want to narrow this down by searching range B1:B10 (I am actually searching B1:B2 now) to find a second value and find it in B2. So the value I want to refence in C2. How do I achieve this. It is similar to your first example except in my case, AX and BX will always be unique and I want to reference cell CX.

Where do I put "tbl (B3:D19)" to define the range of my table?

Hi Oscar, thanks for your help.

As i cannot upload your file with changes, i will try to describe what i mean.

I guess it's important to say that you were so close - the structure of your worksheets was spot on!

Please note, however, that the matching data will not be in the same rows on each worksheet. In your initial attempt, all matching ‘tenant name’ and premises’ are in the same rows of each data worksheet. Everything else you assumed, however, is perfect.

It is also important to note that where there has been a match found in worksheet 1 and 2 for ‘tenant name’ and ‘premises’, this will be unique. In such an event, I would like other data returned from worksheet one, like what has been done in your first model.

As stated previously, i really want a solution that essentially scans each row individually over every row in worksheet 2, and where a match found, other data from worksheet 1 is displayed in worksheet 3. It would be a good if there was a controlled error message (i.e. how you can display desired text in '=if=' functions) where data is not found, so that i can then sort the results page by a particular metric, unless of course you have a more elegant solution.

Also, as I am a naive excel user, why does this type of syntax require “Ctrl + Alt + Enter” to perform its function. Why won’t it work without it? What is so different in function about an ‘array’ formula?

Thanks again Oscar.

Hi Tony,

I´ll answer your questons as soon as I can. You can use this this contact form to upload your file: Contact form

Tony,

Take a look at this file: Tony_v2.xlsx

Tieku,

Array formula:

The formula returns values from cell range $D$3:$D$19 where $G$3 = $B$3:$B$19 and $G$4 = $C$3:$C$19.

I need to return a value from nine columns based on the following criteria:

Matching an Origin Zip Code (one column)

Matching a Destination Zip Code (one column)

Weight of a shipment (values are split out in nine columns based on weight ranges: 0-500, 501-1000, 1001-2000, etc.

To further complicate this, I need to calculate in a 40% discount on the rate returned (weight column) and add a 17% fuel service charge.

HELP!!!! LOL

Karen G,

Interesting question, can you provide some example data and the desired outcome?

Good morning! I'd be happy to. I work in supply chain logistics and am putting together a decision tool to determine the best way to ship goods. We can ship either Full Truck (FT) or Less Than Truckload (LTL). I have a LTL Tariff Schedule which shows the cost of shipping between two different zip codes, based on various weight ranges. The value is 'pennies per 100 weight', and is the first step in calculating the cost for shipping the product. After the weight 'range' is determined, I have to deduct our average discount (40%) and add back the estimated fuel surcharges (17.5%).

I have the calculation part of this done: =(F13923/100)*(500/100)*0.6*1.175

What I'm struggling with is creating a formula to match the two zip codes, and find the correct 'weight' from the column ranges.

It might be easier if you open Excel and enter this information:

COLUMN HEADERS

A:Origin Zip

Origin St

Dest Zip

Dest St

0-500

501-1000

1001-2000

2001-5000

5001-10000

ROW DATA

1566 MA 500 NY55 4629 3575 2723 2406 1728

COLUMN HEADERS

A: Origin Zip

B: Origin St

C: Dest Zip

D: Dest St

E: 0-500

F: 501-1000

G: 1001-2000

H: 2001-5000

I: 5001-10000

ROW DATA

A: 1566

B: MA

C: 500

D: NY55

E: 4629

F: 3575

G: 2723

H: 2406

I: 1728

The result of this Tariff Schedule needs to be pulled into another document, similar to VLOOKUP. I need to use the Origin Zip (our distribution center), the Destination Zip (customer's distribution center) and weight of shipment to calculate the correct cost of each shipment.

COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E

Customer Customer DC Origin Zip Dest Zip Weight

CVS Bessemer, AL 35023 46158 35023 1005

CVS Conroe, TX 77385 46158 77385 500

COLUMN F = Returns Cost of Shipment from Tariff Schedule outlined in previous post above.

I hope this makes sense!!!!

Hi All - I'd like to search on 2 criteria in a list of data held on a second worksheet and where those 2 criteria are satisfied return the results held in a 3rd cell on the secondary worksheet - there may be multiple true results and the list im looking at is arranged in a vertical list - I want the results pulled through and concatenated into a single cell if possible. I've been all around the answer and had almost the right answer but sadly now my brain has exploded with frustration. Any help may result in the provision of beer or chocolate!!!

Karen G,

See attached file:

KarenG.xlsx

Thank you so much! We're almost there!! The calculation is off by one cell/line. Your formula returned 2723 between zip codes 46158 and 35023; however, the real number should be 1826. Your formula returned the weight for 2000 pounds between the first set of zip codes (01566 and 00500).

Origin Zip Dest Zip Weight Cost of Shipment

46158 35023 1005 2723 (your formula return)

Origin Zip Origin St Dest Zip Dest St 500 1000 2000

1566 MA 500 NY55 4629 3575 2723

46158 35023 AL 2025 1826 7864

How can the numbers in Row 1 Cells E-I act as the decider for the actual weight?

Greetings!! We have figured it out and I wanted to share what we did with you. Hopefully this can help someone else too.

We concatenated both Zip Codes to create one unique primary key: 46158 - 35000

Then created a unique VLOOKUP chart in addition to the LTL Tariff Schedule:

Weight Value Columns

500 2

1000 3

2000 4

5000 5

10000 6

20000 7

30000 8

40000 9

The following VLOOKUP formula was created using the zip code primary key and actual weight of the shipment.

=VLOOKUP(I2,'LTL Tariff Schedule'!$E$2:$M$77025,'LTL Calculation'!J2,FALSE)

WORKS LIKE A CHARM!!!!

Thank you so very much for taking time to try to help me. I greatly appreciate it!

Karen G,

I think I forgot to tell you the formulas in the attached file are array formulas.

I hope this picture explains why the formula returned 7864 for cost of shipment:

Origin Zip Dest Zip Weight Cost of Shipment

46158 35023 1005 2723 (your formula return)

Also, see the weight ranges in row 6.

Dear Oscar,

I m working on the below table.

ORDER MODEL MATERIAL QTY STATUS

BOM a s6 1 COMPLETED

BOM b c6 2 NOT COMPLETED

BOM c s6 1 COMPLETED

DEL d c6 3 NOT COMPLETED

EXP a a8 4 IN PROGRESS

DEL b d2 5 COMPLETED

DEL c c6 4 NOT COMPLETED

DEL d s6 7 NOT COMPLETED

DEL e c6 8 NOT COMPLETED

DEL r a8 1 COMPLETED

EXP g d1 5 COMPLETED

EXP r c6 9 COMPLETED

EXP t a8 2 COMPLETED

EXP a c6 1 NOT COMPLETED

EXP b s6 9 COMPLETED

EXP c c6 1 NOT COMPLETED

EXP d a8 4 NOT COMPLETED

I need the status column to be vlooked up on another file by comparing all the remaining 4 columns.(the sheet to be updated carries the 4 columns not in the same order as in the original sheet.. its mixed).. Pls help me with dis..

Thanks

S.Babu

Try concatenating several fields together to make one (unique) primary key. Example PO number and customer number.

S.Babu,

read this post:

Lookup multiple values in different columns and return a single value

I have problem, and o dont know how to solve it, i have data of almost 10000 forms, from which i have to fine 1500, so it is very difficult to dig out 1500 one by one through Ctrl+F, is there any way to to put all the forms number at once and then find all those just by pressing. the prinary key is Form Number

Mohsin Ali Raziq,

I think you will find this post interesting:

Return multiple values using vlookup

Mohsin Ali Raziq,

Read this post:

Quickly search a table using many criteria

Hi Oscar

I have a table on one tab with a list of actions and when they are due to start and when they have been completed with a description. I am creating a cover page on the first tab to show just the last 7 days action descriptions and the next 7 days planned action descriptions for a management summary

I can't seem to work out how I return all the Action descriptions from today and the last 7 days for completed and tomorrow and the next 7 for planned. Can an Array do this?

S.Zeb,

Can an Array do this?Yes, see attached file:

Filter-dates.xlsx

Thanks Oscar, works a treat. Now have a fully functional automatic dashboard of time specific activities on my spreadsheet header page.

Cheers

[...] Records, Search/Lookup, Sort values on Sep.21, 2012. Email This article to a Friend Mohsin Ali Raziq asks:I have problem, and o dont know how to solve it, i have data of almost 10000 forms, from which i [...]

Hi Oscar,

I'm designing a fairly simple sheet to calculate a 'border' around a product . There are 3 different materials that can be used, each with a different price per metre. On the sheet, I have a box for user input as to which frame they'd like to use.

I'm now stuck trying to work out which function will allow the sheet to recognise which size material has been used (I used a drop-down box so the user can make a choice from 3 options) and then calculate the total cost from the option they choose. I have all the values in place (options 1,2,3 and cost per ,metre) but I don't which function to use in the calculation box.

This is probably a really simple operation but I've never had to calculate something like this.

Thanks for any help you can offer

Andrew,

Drop down list in cell C2.

Formula in cell C4:

=INDEX(B10:B12,MATCH(C2,A10:A12,0))

Awesome post, thank you so much this is exactly what I needed!

[...] Match two criteria and return multiple rows in excel [...]

Hello Oscar,

I know very little about excel...and seeking your expert advice regarding the below scenerio....

I got 2 spread sheets.

1st one with 50000 rows and columns upto CJ

2nd one with 3000 rows and same number of columns

I need to extract rows from the 1st spreadsheet using values from column CI from the 2nd spreadsheet.

values are not numericals. How can I do it?

ahmed,

Are you comparing values in column CI in both sheets?

Perhaps you can use the method described in this post:

Quickly compare two tables in excel 2007

Use countif instead of countifs.

Dear Oscar,

Can you help me to Retrieve a row details based on three conditions, out of that two will have in the same column and the third one on another, thanks in advance. Regards;

Dear Oscar,

I myself modified your formula to suit my requirements of 3 conditions, however kindly advise my if sorting can be done based on Ascending.

Example:

=INDEX(tbl,SMALL(IF(OR(COUNTIF($C$12,$C$40:$C$84),COUNTIF($C$13,$C$40:$C$84))*COUNTIF($D$19,$D$40:$D$84),ROW(tbl)-MIN(ROW(tbl))+1),ROW(A1)),COLUMN(A1))

Regards

Sudhakar

Hello Oscar,

First off, Thanks for all the great help you do on your website. This website has helped me countless times.

Here is my questions. It's similar to this post.

I wrote a vba macro that searches a column in a data range for a specific number. When it finds any row matching that number it copies that row and pastes it onto another tab, making a list. My goal is to allow people to make changes to that row of data (on the new tab) then when finished, update those specific rows on to the original data range via a "submit button". The problem i'm facing is that when they decide they want to update these rows, i need update the rows that match two specific columns. I'm aware of how to do the replacing of the row when I find the rows that match, but i'm unaware of how to find those rows and have it return to me the row number i need to replace.

Example of main data range

Project Project# Employee Hours

BestBuy 54511 Miguel 20

BestBuy 54511 Martha 10

BestBuy 54511 John 40

WalMart 14513 John 30

WalMart 14513 Martha 10

WalMart 14513 Miguel 70

Example of results when they search for a specific Project#

Project Project# Employee Hours

BestBuy 54511 Miguel 20

BestBuy 54511 Martha 10

BestBuy 54511 John 40

Example of changes they wish to submit back into master (AT THIS POINT IS WHERE I NEED TO FIND THE ROW NUMBER OF ROWS THAT MATCH PROJECT# & EMPLOYEE)

Project Project# Employee Hours

BestBuy 54511 Miguel 70

BestBuy 54511 Martha 0

BestBuy 54511 John 30

Example of UPDATED main data range

Project Project# Employee Hours

BestBuy 54511 Miguel 70

BestBuy 54511 Martha 0

BestBuy 54511 John 30

WalMart 14513 John 30

WalMart 14513 Martha 10

WalMart 14513 Miguel 70

Hope you can help me out, Let me know if you have any other questions or need me to clarify something.

Hi Oscar,

the definition is: COUNTIF(range,criteria)

but from your formula it looks like you are using:

COUNTIF(criteria,range)

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

What you are doing on your awesome blog posts does work so I was just wondering if you could shed more light on it?

Thanks!

Patrick

hello Oscar sir,

i want your argent help

i want to create 1 formula

ex.

if i get value in one cell 2 then it will ans 3

i get 4 or 5 it will return 6

if i get value 7 or 8 then it will return 9

so it will possible then plz reply fast

Siddharth,

Array formula in cell B2:

thank you so much, it solves my problem beautifully

gilgamesh,

Thank you!

Hi Oscar,

Thanks very much for this informative guide. I found it very helpful, and although I just need a bit of help with a certain problem.

I used your array formula with great success to find the search results from multiple critera. However my problem is modifying your formula. In the above example you have shown us, you have two criteria. And you distinguish the two criteria by using *.

My question is: what would you do if you don't know the predetermined number of criteria. So lets say the person searching only specifies security and not date. Or only date, and not security. Or maybe both. The problem is you don't know from before hand.

How would you go about solving this probem?

Your help is greatly appreciated, thanks so much!!

Rashid,

Great question! This post explains to some degree what you are asking for:

Lookup using multiple conditions

Thanks for the quick response Oscar,you have greatly helped me many times over.

I will take a look at the link provided, thanks very much!

Rashid,

read this post:

Lookup with an unknown number of criteria

https://www.get-digital-help.com/2013/10/18/lookup-with-an-unknown-number-of-criteria/

I was wondering if you can recreate this in Numbers on iPad. I am trying to create a cross platform version, but it says the formulas are not compatible. Can somebody help me with this? Not just this article, but everything dealing with popup menus as well.

Jon E,

I have no clue.

[…] lookup with multiple criteria. Indeed, I think the tutorial in the following link is useful: Match two criteria and return multiple rows in excel | Get Digital Help - Microsoft Excel resource (Using the sample from the above link) BUT sometimes in my case, the Cell G3 and G4 may contain a […]

Dear Oscar,

Let me start by saying thank you for your help.

I have a challenge in which I have been trying to fix for long, but never managed to make it work. Hope you can help.

Excel: 2011 MAC

Info:

Three columns A, B and C of Table "TBL"

Search Criteria: $D$11

Goal:

To load rows from column B into data validation list based on Column A matching $D$11

Can this be done?

Regards

Monzer George Yazigi,

Yes, it can be done. But you need a helper column.

Extract-values-and-use-them-in-a-drop-down-list-data-validation.xlsx

Thanks for your kind help.

Highly appreciated.

Hi Oscar,

I have an issue with a payroll schedule.

The payroll schedule has a number of columns: Employee Code, Pay Code, Hours and Rate. Each employee code could have from 1 to 5 different Pay codes. I want to lookup the employee code, if found then look for pay code number 1, if found; return the code, hours and rate, if not found; return blank. Next row, (same employee); look for paycode number 2 etc to paycode 5. Then next employee etc.

Thank you.

Adam,

I believe this post is helpful:

Return multiple records

Hi Oscar,

Hi,

I have a football data spreadsheet that lists all games since 2000/01 (14 seasons). Every line (game) shows date, home team, home team goals, away team goals, away team & result - H, D or A. Columns are A, B, C, D, E, & F.

What I would like to do is for each occurrence of home team & away team to add the result to the Head2Head field (Column G). So if the two teams have played each other for the last 2 seasons and the home team won both, the Head2Head cell would show HH. Similarly if they have played for 6 seasons it could display something like HHDADH. If they have played more than 6 seasons it should drop the earliest to show the latest 6. The earliest result would be on the left of the cell and the most recent on the right. In the last example if a new game produced a draw HHDADH would become HDADHD.

I am not an excel expert so I'm finding it quite difficult to adapt some of your suggestions on the blog.

Hope I have explained it clearly enough.

Thanks in advance..

[…] table when referencing. This should work for you! --------------------------------- Check out Match two criteria and return multiple rows in excel | Get Digital Help - Microsoft Excel resource for more information on how this array formula […]

Hi Oscar,

I am trying to use similar formulas to these to produce a to do list. I deal with FOI requests, and they get reminders sent out at 10, 15, 17 and 19 days, and are due for response on the 20 day (or earlier).

I want to produce a list on the first tab, looking up info from the log (2nd tab), where a request is due it's equivalent reminder and hasn't already had it, or is due for response that day and hasn't yet been closed.

Tab 2 "RequestLog" - Column B is the reference number - this is what i want in the to-do list

Columns V - Y have a P (tick in wingdings2) if their reminder has been sent

Column AA - Has the closed status of the request (is blank if no response has been sent)

Column AX - Has the days remaining until deadline

On tab 1 i have a column for each of the reminders, i.e. for 10 days I want the reference to show up when AX is 5, V (the 10 day column) is blank and AA is blank. This would be the same for all reminders, just changing the "between" part to relate to the days (i.e. 15 days: 3, 17 days: 1, 19 days: =1)

For the 10 day reminder list, I have used the following formula, which will give me the first instance, but when i copy it down, it just says the same again & again:

=INDEX(RequestLog!$A$8:$AX$1008,MATCH(1,(RequestLog!$AA$8:$AA$1008="")*(RequestLog!$V$8:$V$1008="")*(RequestLog!$AX$8:$AX$1008=5),0),2)

I think i am just missing one tiny piece that will allow it to give what i need. Can you help (and is this enough info)?

Preferably, i would want this to hide errors (N/A's), but i can live with having them there...

Thanks in advance for anything light you can shed...

Sorry, have just re-read my post and realised i pasted completely the wrong formula. this is what i have so far managed to get to show the first instance of a FOI requiring a 10 days reminder:

=INDEX(RequestLog!$A$8:$AX$1008,MATCH(1,(RequestLog!$AA$8:$AA$1008="")*(RequestLog!$V$8:$V$1008="")*(RequestLog!$AX$8:$AX$10085),0),2)

For the other reminder columns, all i would need to change is the numbers after .

I hope this all makes sense.....

Still the wrong one! 3rd time lucky...

=INDEX(RequestLog!$A$8:$AX$1008,MATCH(1,(RequestLog!$AA$8:$AA$1008="")*(RequestLog!$V$8:$V$1008="")*(RequestLog!$AX$8:$AX$10085),0),2)

Ok, this website does something to my formula once i press "add comment! Apologies for the multiple comments/posts.

Basically, for the column AX there should be two brackets, one less than 11 (looking at the above the website doesn't post that symbol) and the other greater than 5.

My question is more about the quantity of criteria, I have a table with a number of types, product, ref numbers etc. and I want to create a separate sheet that has a look up function so I can find a single item that matches 3 different criteria (there are 100's of items some with very minor differences). I know I can do this with MATCH and INDEX but I want to know if there is a way to find the same solution by only entering 2 or the criteria (i.e. all three criteria are available to fill but I only need to enter two for the solution to return.

Thanks in advance.

Hello. I found your article great, as it was just what I needed to figure out. Unfortunately I couldn't get it to work! :( Am I being dim, but the CountIf(criteria, range) just counts the number of instances of of that criteria within the range, does it not, so when I use your splitting up example of (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}

...just returns '4' for me.

I am not used to working in arrays yet, but I hope to learn! Thank you in anticipation.

This is fantastic. I would like the table to be longer, say through B3:D100. I changed the tbl to include this and the formulas in the countifs to include 100, but it is not coming up with the right information in the search results. Can you help?

Thanks.

PLS HELP ME TO SOLVE THE BELOW:

I NEED TO SEARCH TWO WORKBOOKS, IF A OF 1ST WORKBOOK MATCHES WITH B OF 2ND WORKBOOK THEN IF THE QTY OF C IN 2ND WORKBOOK IS GREATER THAN 1, RETRIVE A RESULT AS 1 OR ELSE 0

Hi Oscar,

Your blog is fantastic. It makes so many things about excel much clearer. I have used this array formula successfully to get information based on 2 criteria and return the outupt in columns, except it does not work past the first row:

{=INDEX(Table24, SMALL(IF(COUNTIFS($A3, customerID, J$1, companyList), ROW(Table24)-MIN(ROW(Table24))), ROW(I2)), 23)}

I removed the +1 you had inside the small because it was returning the next row down for some reason but even when I add it back in the formula does not work past the first row.

I read through all the comments for this thread and your thread on VLOOKUP returning multiple values which seemed to address this problem but none of the suggested tweaks seem to work for me.

Please help.

Good evening Oscar!

Your invaluable tips have been helpful for many enthusiastic excel users like me.

Could you please shed light on a scenario: I want to create an excel sheet "B" which feeds itself from data in sheet "A"(which we don't have to play with). The crux is sheet "A" gets periodically updated and has fixed number of rows; and sheet "B" needs to be auto-updated in its consecutive rows, with it looking up value in sheet 'A" and pasting only new data from sheet 'A". This means number of rows of sheet "B" will be constantly increasing. Can we use some formula that can update sheet "B" with new value from sheet "A" every time we drag down formula in sheet "B"?

Thanks a lot,

Prabin

Prabin

with it looking up value in sheet 'A" and pasting only new data from sheet 'A". This means number of rows of sheet "B" will be constantly increasing.If I understand you correctly, you can't save old data to a sheet, using a formula. You need a macro to do that.

Thank you for your kind attention Oscar. Any suggestion on what type of Macro could achieve that?

Regards,

Prabin

Hi Oscar,

Great Blog!

I discovered that the formula does not work if you are looking for the same value in different columns.

What I mean is, if I have a table and the same values in two different columns and I would like to return all rows that have the needed value in one of the column the formula will not return anything.

If I change it to be a different value everything works.

Do you happen to know how this could be fixed?

Thanks!

Kobi S.

Here is the original formula:

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

If you want to look for the same value in two different columns, use this formula:

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

It looks for value in cell G3 in both cell range B3:B19 and C3:C19, if both cells match on the same row the entire record is extracted.

Hi Oscar,

I need to implement a logical OR meaning:

(0,0) = Do not report

(0,1), (1,0) & (1,1) = Report line

I tried changing the "*" between the countif to "+" or using countifs but it did not work.

Any thoughts?

Kobi S.

You are right, use a + sign like this:

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

Remember it is an array formula.

Worked like a charm!

Thank you!

Hi

Thank you very much!

It's working.

the same security data , what will be the formula if i want , the records which belong to security B and price greater than $ 40.0

Hi, could you please help me, i need to find a formula that extract and list the different numbers that match with 2 conditions.

To find the work number that match with the date and the area. Thank you,

Graciela

If security is your area condition and date is your date condition and your work numbers are in column D (price in example at the very top of this article)

Array formula:

=INDEX($D$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)))

Hi Oscar,

great page but I still need help.

I have two tables and I have to find all Connectors/Cross sections combinations in Table 1 and write the terminal number in Table 2 column E

Table 2 Column C "First match"

{=INDEX($A$3:$A$7;MATCH(1;(FIND(","&A12&",";$B$3:$B$7)>0)*(FIND(","&B12&",";$C$3:$C$7)>0);0))}

Table 2 Column D Count

{=SUMPRODUCT(ISNUMBER((FIND(","&A12&",";$B$3:$B$7))*(FIND(","&B12&",";$C$3:$C$7)))*TRUE)}

My problem is to join all found results/terminals together in one cell.

Do you have any suggestion for me?

A B C D E

1 Table 1

2 Terminal Connectors Cross sections

3 Nr1 ,S1,S2,S3, ,0.25,0.75,1.0,

4 Nr2 ,S4,S5,S3, ,0.25,1.0,

5 Nr3 ,S6,S7, ,0.35,0.5,

6 Nr4 ,S4,S8,S9,S10, ,0.5,1.0,

7 Nr5 ,S1,S3, ,0.25,0.75,

8

9

10 Table 2

11 Connector Cross section first match count Expected

12 S1 0.75 Nr1 2 Nr1,Nr5

13 S2 0.5 #N/A 0

14 S3 0.25 Nr1 3 Nr1,Nr2,Nr5

15 S4 1.0 Nr2 2 Nr2,Nr4

16 S5 0.25 Nr2 1 Nr2

17 S6 0.75 #N/A 0

18 S7 0.5 Nr3 1 Nr3

19 S8 0.5 Nr4 1 Nr4

20 S9 0.5 Nr4 1 Nr4

21 S10 1.0 Nr4 1 Nr4

22 S1 0.25 Nr1 2 Nr1,Nr5

Thanks in advance

Stefan

Stefan,

the TEXTJOIN function allows you to concatenate values based on a condition:

https://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/

https://www.get-digital-help.com/2016/06/07/textjoin-function/

Hello Oscar,

thanks for the links.

My problem now is that I do not get the result matrix before joining.

{IF({1;0;0;0;1};$A$3:$A$7;"")}

should return

{Nr1;"";"";"";Nr5}

But it returns only "Nr1"

What I´m doing wrong?

Thanks again for you help.

Hi Oscar,

I´ve got it know. :-)

Thanks for your help.

Stefan

The answer to this is entering the formula as an array formula, if anyone else has this question.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.Hi Oscar,

Thanks for writing this guide. Can I know for the security file what if I want to change the criteria to return results for Security B with prices less than $65? How can I change the formula in cell H9? I've tried changing countif part of it to "<65" but it doesn't seem to work. Thank you!

Winnie Low.

try this:

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

Hi Oscar,

Thank you for the detailed explanation, however, I'm still struggling with applying this formula to the following situation:

Tab 1

Col A Col B Col C

20 A Jake

20 A Jake

20 A Jim

20 A Jake

20 A Mike

20 B Jake

30 A Mike

30 C John

30 C Mike

Tab 2:

Col A Cell B1: A / B / C

20

20

20

20

30

30

30

In tab 2, I need to return the name from column C when col A & B Match the values of col A & cell B1. However, if I return the first name, I want the second return value to be the NEXT DIFFERENT name. Do you have any idea how to assist? It would be amazing if you could help, since I've been struggling with this for a while.

Thank you in advance. Kind regards, Max

Hi Max,

I believe you are looking for this article:

https://www.get-digital-help.com/2010/09/19/vlookup-with-multiple-matches-returns-a-different-value-in-excel/

Hi Oscar,

I have a basic question as I am not able to wrap my head around COUNTIF($G$3, $B$3:$B$19)

Does the range not come up first followed by the criteria? I made a mini table to understand but do not get why we switch the range and the criteria. Please let me know

Adi

Hi Adi,

Yes, the range first and then the criteria.

=COUNTIF (range, criteria)

The COUNTIF function returns an array if you use multiple criteria provided you enter it as an array formula. The array contains the same number of values as there are criteria.

Check out this article for examples:

https://www.get-digital-help.com/2013/03/27/countif-function/

Hello Oscar,

I have adapted your formula to a case of my own but for some reason is not counting the first entry in each of my scenarios in which I am using it. Say I should have 8 results. It skips the first and only shows me the next 7.

Hanna,

Make sure the cell reference in the ROW function points to cell A1 on the first result. The formula will change when you copy the first row of cells and paste them to rows below.

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

A better formula is:

=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), ROWS($A$1:A1)), COLUMNS($A$1:A1))

This formula won't break if you insert rows or columns on the same worksheet.

Hi Oscar

what about below. Is there any similar post I can try out?

Criteria:

First these columns has to be exact matched -(($C$2:$C$12=O2)*($D$2:$D$12=P2)*($E$2:$E$12=Q2)*($F$2:$F$12=R2)*($G$2:$G$12=S2),$B$2:$B$12),1))

Second criteria Column N & B2:B12 : The exact match value the date in column N must not overlap after in B2:B12 in the matched value. See the example above that I have manual enter the map ID

Index match value at A column (Running ID number 0001,0002,0003...) return the A value to column M if above match is matched.

Hi,

Thank you for your video, it has been extremely helpful. I've gotten to the SMALL function piece and I have 3 criteria I'm trying to match on. I have 5 rows that should have data and return the row numbers in the IF statement portion. Am I doing something incorrectly? When I put it in Excel I am using Ctrl+Shift+Enter

=SMALL(IF(COUNTIF(Summary!$C$1,'Cancel Reasons'!$B$3:$B$1173)*COUNTIF(Summary!$C$2,'Cancel Reasons'!$D$3:$D$1173)*COUNTIF(Summary!$C$3,'Cancel Reasons'!$C$3:$C1173),MATCH(ROW($B$3:$B$1173),ROW($B$3:$B$1173)),""),ROWS($A$1:A1))

Limited understanding and I've been struggling with returning a list based on multiple criteria but , thanks to this page, I think I'm making progress.

I'm trying to generate a list based on whether a specified date (in cell H8)between the start dates and end dates for rows on a separate sheet (Date Input). The formula below works but it's based on matching the start dates and matching the end dates. How would I change this to = end dates . I think I'm getting confused 'cos a countif would usually be range first then criteria?

=IFERROR(INDEX('Data Input'!$A$39:$H$536,SMALL(IF(COUNTIF(Sheet1!H$8,'Data Input'!$G$39:$G$536)*COUNTIF(Sheet1!H$8,'Data Input'!$H$39:$H$536),ROW('Data Input'!$A$39:$H$536)-MIN(ROW('Data Input'!$A$39:$H$536))+1),ROW('Data Input'!A6:D6)),COLUMN('Data Input'!A6:D6)),"")

Hey Oscar.

Fantastic tip thank you! I've managed to integrate with a greater than option, which is working, but I wondered if it was possible to re sort the results rows based on the numerical value of one field.

e.g. search

critera 1

results

result 1 = 8

result 2 = 12

result 3 = 5

result 4 = 22

result 5 = 2

I would like it to be

result 5 = 2

result 3 = 5

result 1 = 8

result 2 = 12

result 4 = 22

Dear sir,

Please help me i need a formula...if my data is in a Colum which is in different cell ..and same data value is down side of coloumn in diffrent row..which is also in diffrent cell...

i need sum of value which three cell data in a cell......

for example....

COLOUM DATA 1 2 3 16 18 20

SUM VALUE 50 500 120 240 240 240

COLOUM DATA SUM VALUE

1-2-3 670

2-3-16 860

3-18-20 600

=COUNTIFS(Roster!J5:J440,'Shift Wise Strength'!A26,Roster!K5:K440,'Shift Wise Strength'!B26,Attendance!S5:S440,"P")

Can someone help me with this ?

why am i not able to get a count on sheet3 of a value from sheet 2 while matching criteria's from sheet 1 & 2 both.