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
Related posts:
Vlookup with 2 or more lookup criteria and return multiple matches in excel
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
Lookup values in a range using two or more criteria and return multiple matches in excel
Match a criterion and extract multiple corresponding table headers
Recommended categories
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 *.xlsx
organize-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
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.