Match two criteria and return multiple records
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 Indexmatch 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 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
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.
Array formula in F9:
Watch a video where I explain the formula
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.
Alternative array formula in F9:
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}
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
How to use the COUNTIF function
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}
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Step 3  Get table value using row number
Array formula in cell F9:
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
becomes
=INDEX(tbl, SMALL({False, 2, False, False, False, 6, False, False, False, False, False, False, False, False, False, False, False}, ROW(A1)), COLUMN(A1))
becomes
=INDEX(tbl, SMALL({False, 2, False, False, False, 6, False, False, False, False, False, False, False, False, False, False, False}, 1), COLUMN(A1))
becomes
=INDEX(tbl, 2, 1) and returns "Security B"
Gets a value in a specific cell range based on a row and column number.
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
Match two criteria and return multiple records [Excel defined Table]
 Click black arrow next to header "Security".
 Select the items you want to filter.
 Click black arrow next to header "Date".
 Make sure only 122009 is selected.
The image above shows both conditions applied to the Excel defined Table.
Recommended articles
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.

Click "Advanced" button, a dialog box appears.
 Click radio button "Filter the list, in place".
 Click "Criteria range:" field and select cell range B2:D3, see image above.
 Click 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 ORlogic instead of ANDlogic 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 […]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F  I to fill with the […]
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 […]
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) 6972395
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.getdigitalhelp.com/2010/02/28/combinedatafrommultiplesheetsinexcel/
/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.getdigitalhelp.com/2010/03/24/lookupusingtwocriteriainexcel/
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.getdigitalhelp.com/2009/10/25/howtoreturnmultiplevaluesusingvlookupinexcel/
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 *.xlsx
organizeinformation.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 nonblank 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, MidAtlantic, 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 "=f5d5"(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.