## Match two criteria and return multiple rows in excel

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

- Select the range

- Click "Insert" tab
- Click "Table"

- Click OK

- Click "black triangle" on Security header.
- Select one or more securities
- Click "black triangle" on Date header.
- Select one or more dates

### Array formula

**Array formula in F9:**

### Alternative array formula in F9:

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

**Named ranges**

tbl (B3:D19)

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

(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

### 136 Responses to “Match two criteria and return multiple rows in excel”

### Leave a Reply

**How to add vba code to your comment:**

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

your code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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

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: http://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: http://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: http://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:Download 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

sathishrosario@gmail.com

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

sathishrosario@gmail.com

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.

Download 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 clicking. 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

http://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