Table of Contents Overview Arguments Approximate match Horizontal and vertical lookup Horizontal and vertical lookup - INDEX + MATCH HLOOKUP - multiple tables VBA Example Download excel *.xlsm file Functions in this post Overview The HLOOKUP function lets you search […]

## Archive for the ‘Search/Lookup’ Category

Table of Contents Overview Arguments Approximate match Horizontal and vertical lookup Horizontal and vertical lookup - INDEX + MATCH HLOOKUP - multiple tables VBA Example Download excel *.xlsm file Functions in this post Overview The HLOOKUP function lets you search […]

Table of Contents Overview Arguments Approximate match Related tables Dynamic arguments INDEX + MATCH VLOOKUP error VBA Example Using multiple conditions in VLOOKUP Download excel *.xlsm file Functions in this post Overview The VLOOKUP function lets you search the leftmost […]

Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short example:A1: Cash in deposit (Branch A t/t) A2: Borrowed from Corp. A A3: Interest payment A4: Int.panalty pmt A5: Prin. […]

A regular expression is a pattern containing specific characters to search for sub-strings in strings. The patterns are at first glance hard to understand but with a little bit of practice very useful. Imagine you want to extract phone numbers from […]

The table of contents below lets you quickly navigate to the formula you are looking for. The excel 2016 formula lets you concatenate all values in one cell and it works only in excel 2016 because of the TEXTJOIN function. […]

I am trying to build a regular expression that matches cell references in a formula. A regular expression is a sequence of characters that define a search pattern, according to Wikipedia. This is the custom function I am using to […]

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value? Answer: The data set above […]

Denis asks: Hello Oscar, thank you for sharing you knowledge and helping us with these excellent formulas. I have a case i could really need your help with: The following Table shows a history of names, stati and the date […]

La Thăng asks: I want to find day if given date and week, for example : if given Tuesday, 32th week, 2015 >>> how to use function to point out 4/8/2015 ? Answer:

This vba macro lets you search for zip files in a folder. Then unzip those files to a folder you specify. It continues with sub folders until all zip files have been unzipped. What happens when you execute the macro? Select […]

John S asks: I would like to find the dates MM/DD/YYYY missing in a set of date ranges. I haven't been able to find any luck on -line with this. example: 2/3/2005 - 2/5/2005 2/7/2005 - 2/9/2005 _____________ Missing dates: […]

The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" or "www". It grabs the hyperlink even if you have a hyperlink function in a cell. Example, this sheet has two […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to total? (2): Multiple Solutions. The MMULT function is used in really clever way, I thought that function was pretty much useless. […]

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell D4 returns the value of the longest consecutive sequence in column A. Cell F3 returns the the length of the shortest consecutive sequence […]

In my last post I showed you how to find a sequence of values. The array formula extracted the row of the first found sequence, see below. Array formula in cell F3: =MATCH(1, COUNTIFS(E3, B1:B23, E4, B2:B24), 0) What I didn´t […]

This array formula finds the sequence "Axis Chemical Co." (cell E2) and "Southern Railway" (cell E3) in two consecutive cells in column A and returns the row number in cell F2. Array formula in cell F11: =MATCH(E2&E3,$A$1:$A$23&$A$2:$A$24,0) The second argument […]

Sam asks: S/N RailCorp Ref Number Date In 77203 HRC mod program 10377 24/05/2011 77204 HRC mod program 10285 20/04/2011 77697 HRC mod program 10489 5/07/2011 77698 HRC mod program 10554 8/08/2011 77699 HRC mod program 10408 8/06/2011 77700 HRC […]

Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is easy. It copies all table headers and data to sheet 2010, cell A13. Copy a filtered table The macro above […]

Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated the shortest path between a start cell and an end cell in a maze, and it did that using only […]

I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells . It demonstrates how to merge two different cell ranges dynamically and that is it. The following examples merge data tables with […]

This animated picture shows you the most urgent work orders for a location. Type a location in cell F3 and the formula in cell G3 extracts the most urgent value for that particular location. The values in column C are […]

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about finding the last matching value in a sorted list. It got me thinking how to find the last matching item in […]

Today I´ll show you how to search all excel workbooks (xls, xlsx, xlsm) in a folder for a text string. The macro creates a new sheet containing the search result. Each result contains a link to a cell where the […]

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to return the value with a prefix order would it possible? If not can I just add another column in the […]

cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in […]

Dave asks: Hi Oscar, This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. However I have been trying to modify the array formulas in the 3rd and 4th posts of that series to […]

Rashid asks: 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 […]

Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. With a scroll bar or spin buttons you can accomplish that easily. Clicking the up or down arrows on the […]

rahul jadhav asks: How can we identify any protected excel file before opening using macro VBA (e.g. i have a folder with multiple excel files i need to find out using macro how many files are password protected before opening […]

Rahul Jadhav asks: I have many excel files with multiple sheets and each excel sheet has many formula which are starting from perticular word e.g. FDS, FDSB, etc some formula has FDS, FDSB occur in the middle of the formula. […]

If you often copy data sets or tables, the following technique might be interesting! The animated gif shows two hyperlinks, the first one selects a data set, the second one selects an excel defined table. Adding more rows or columns […]

Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take you to the first empty row in a data set. Formula in cell C2: =HYPERLINK("[Quickly jump to last row using […]

Find a value and return a corresponding value is really easy with Excel´s INDEX and MATCH functions. Did you know that you also can quickly "jump to" and select that value in a table using the HYPERLINK function? RAJ.A.D asks: […]

A blog reader asks: I need an excel 2010 macro, where I can enter a number (value) into cell B1, click on button below, and the page will automatically go to the corresponding shape, with that same number. Answer: Enter […]

Column B contains random dates. The array formula in column D returns consecutive dates from column B. Array formula in cell D3: =IFERROR(SMALL(IF((COUNTIF($B$3:$B$8, $B$3:$B$8+1) + COUNTIF($B$3:$B$8, $B$3:$B$8-1))>0, $B$3:$B$8, ""),ROW(A1)), "") How to enter an array formula Select cell D3 Copy […]

Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter a name and excel shows all details and a photo. Answer: Formula in cell C4: =INDEX(Data!B2:B9,MATCH(C2,Data!A2:A9,0)) Formula in cell C6: […]

Debraj Roy asks: Hi Oscar, I failed to search for "Ask" Section.. so submitting my query here.. Apologize for hijacking someones post,, and also for cross-post.. http://chandoo.org/forums/topic/lookup-using-multiple-condition Can You please help me to create a drag-able FORMULA to get Margin.. […]

krish asks: I've a query in case of two columns of dates and two columns of data. Calling columns A and B as data(text), C and D as Dates(dd/mm/yy), IF column D's date is not empty and matches to the […]

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.Where I have a date of say, […]

Elizabeth asks: Hi Oscar, Need help with a formula Please. I need to figure out the date range of a cell. So if cell "E2" has a date of 11/23/2012 then that would need to fall under date range of […]

Marc asks: How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, but only want to find the Min and Max on the largest/top 100 non-duplicate values. Array formula in cell […]

Table of contents VLOOKUP and a condition VLOOKUP and a table VLOOKUP - Select a column with a drop down list VLOOKUP and two conditions (date range) INDEX and MATCH VLOOKUP and a condition The animated picture above shows you […]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When you click the cell that contains the HYPERLINK function, Excel goes to that location in the table. Formula in cell […]

It can be really hard trying to follow a lookup in related tables. I will show you how to use conditional formatting for easy identification. If you add more rows to the tables, the conditional formatting expands automatically. Conditional formatting formula applied […]

I have written a few posts about two related tables and today I am going to show you how to work with three related tables: Lookups in three related tables and return multiple values Filter unique distinct values from three […]

In a previous post I described how to do lookups in a related table. In this post I am going to show you how to extract unique distinct values and duplicates from a related table. Unique distinct values Duplicate values […]

The "new" excel 2010 powerpivot feature and DAX formulas lets you work with multiple tables of data. You can connect tables to each other by relationships. When relationships are made nothing stops you from doing lookups to related values and […]

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email address appears in all three (not two out of three) lists then place it in the duplicate column. Also I […]

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 have to find 1500, so it is very difficult to dig out 1500 one by […]

Nena asks: Hi Oscar,I've been trying to find the solution for my lookup problem for a while now and you seem like the right person to ask... Your lookup code works great (thanks) but I need to do two or […]

My previous post Excel calendar (vba) used vba and formulas to extract events. This post demonstrates how to filter a table using a calendar. Select a date on the calendar and events on that specific day are automatically filtered. I would certainly have […]

Phoneix asks: I have a worksheet(#1) and I want to populate the amount Column with data in another worksheet in the same workbook, Based on 2 criteria: State and Date Worksheet #2 Table A1:D19 = EST ColA ColB ColC ColD […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of dates and the y is names. The table values indiciate whether or not the employee is scheduled to work (i.e., […]

Vikas asks: i need to extract the headers from a grid based on value in left most column example row header ---> a b c d e data 1 1 2 2 2 2 1 1 1 1 1 2 […]

Table of contents Lookup multiple values in different columns and return a single value Lookup multiple values in different columns and return multiple values Lookup multiple values in different columns and return a single value S.Babu asks: Dear Oscar, I […]

Table of Contents Search for a text string in a column and return multiple adjacent values Search for a text string in a column and return multiple adjacent values corresponding to the number of matching values Search for multiple text strings […]

I would like to show you how to search a table using conditional formatting. The criteria highlight matching column and rows. I am going to explain how to create the highlighting and the conditional formatting formulas behind. Setting up the […]

Bill Truax asks: 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 […]

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles "ON Work" The drop down menu is in cell B50 I then need to be able to look up whatever […]

Chirag asks: I want to separate numbers from following text: Abc123bx45 as a result 123 and 45 should be in different cells. This is a monster size array formula in cell C5 and it was created in excel 2007. The […]

In this post I am going to demonstrate how to quickly apply a filter to a table. I am using the selection change event to apply the filter. Click on a cell in a table and the cell value is instantly […]

This post describes how to lookup values in multiple cross reference tables using a user defined function. This custom function accepts an arbitrary number of range arguments (table ranges). The custom function returns the first match in each table. Array […]

This post describes how to search visible values and return multiple values from a table. Some rows are hidden because of table filters. I am not using the vlookup function in this formula. Example, Array Formula in cell B14: =INDEX($C$2:$C$9, SMALL(IF($B$11=(IF(SUBTOTAL(3, […]

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and sheet2 contains table 2. The search value is Pen and is in cell B9. Array Formula in cell C9: =IFERROR(INDEX(tbl_1, […]

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from one sheet (A) into another sheet (B). I need excel to search through the dates in sheet A to find […]

wackyboy asks: My database is as shown, where I have company abc sells siemens, omron and mitsubishi and company qwe sells omron n siemens. Company Products abc Siemens Omron Mitsubishi qwe Omron Siemens asd Omron Moeller zxc Mitsubishi Omron So […]

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" but, if either of the criteria for "Middle Name" or "Last Name" will suffice. Also, i don't want repeated values […]

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a unique distinct list based on each condition. I remember reading that Excel has difficulty with these type of or conditions […]

This post demonstrates how to automatically add new values to a drop down list and a chart. This tutorial contains three steps: Create two named ranges Create a drop down list Set up a chart Create two named ranges Click […]

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? my sheet is setup as follows A B C D E 1 Section Category item flavor size 2 food Coffee Espresso none Single 3 food […]

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, I have to match a client name, and add all the sales totals: clientA 10 clientA 10 clientA 10 clientB […]

Today I am going to show you how to quickly compare two tables using conditional formatting. We are comparing two price lists from the year 2010 and year 2011. To make things more interesting, price list 2011 is not sorted. […]

In this post I will describe a basic user defined function with better search functionality than the array formula in this post: Fuzzy vlookup. The user defined function searches for a cell with as many characters matching as possible. It is […]

Murlidhar asks: How i search text in cell within two dates i.e st.Dt D1 end dt. D2 Search "soft" in entire column for" Microsoft" Answer: Array formula in cell F10: =INDEX($B$3:$D$29, SMALL(IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), ROW($A$1:INDEX($A$1:$A$1000, ROWS($C$3:$C$29))), ""), ROW(A1)), COLUMN(A1)) How to […]

The array formula in this blog article has no "Fuzzy logic" nor vlookup function. But it can return names or words arranged differently and with minor misspellings just like a user defined function with "Fuzzy logic". There are too many […]

This udf extracts all words containing a specified string. Example, Cell range B1:M50 contains random sentences. I have inserted some random emails in this range. VBA code Where to do I copy the code? Press Alt-F11 to open visual basic […]

This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change criteria by editing row 3 and 4. Conditional formatting formula: =SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B7)*($B$4>=$B7))*(IF(ISBLANK($C$3), 1, $C$3=$C7))*(IF(ISBLANK($D$3), 1, $D$3=$D7))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E7)*($E$4>=$E7)))) How […]

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in row 3. A row is highlighted if: Date criterion is found in column B or Color criterion is found in column […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the second closest value (or more). Conditional formatting formula =OR(ABS($C6-$C$2)=SMALL(ABS($C$6:$C$10-$C$2), ROW($A$1:INDEX($A:$A, $C$3)))) This is not working in excel 2010 and later […]

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series of phrases. Every phrase contains one color in the phrase. The goal of the challenge is to use the lookup […]

Array formula in D4: =INDEX($A$1:$A$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell D4 and paste it down as far as needed. Array formula in E4: =INDEX($B$1:$B$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + […]

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above but it didn't work for me and I can't figure out how to adjust it to accomodate my needs. Here […]

magneticone asks: Any idea how to adapt the formula to do a conditional (i.e. comma-separated) merge of the data from multiple columns, in the case that a single row has emails spread over several columns? You can find magneticone´s question […]

Arielle asks: I have to search for a cell in a table and then display the column title. search value in cell e1: AA table in cells A1:C6 A1:x B1:y C1:z A2:BB B2:CC C2:DD A3:AA B3:GG C3:AA A4:CC B4:BLANK C4:EE […]

How to filter numbers inside ranges in column E and F Array formula in A2: =SMALL(IF(FREQUENCY(IF((COLUMN($A1:$U1)<=End)*(COLUMN($A1:$U1)>=Start), COLUMN($A1:$U1), ""), COLUMN($A1:$U1))>0, ROW($1:$21), ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell A2 and paste it down as far a sneeded. Alternative […]

In this post, I have created som random time ranges. We are going to use these time ranges to extract empty hours between ranges. Random date/time ranges: I have reused the same weekly schedule as in this post: Highlight specific […]

Question: I have given up trying to figure out the following problem as it seems over my head, and am hoping there is a kind soul out there can help me out with the solution. I am trying to setup […]

In this post we are going to extract multiple text values. We are looking for names and the criteria are two or more states (cell B18:B19) and two or more months (cell D18:D19). But first there is something we can […]

I found a question in the comments section. You can find the question in this post: Lookup values in a range using two or more criteria and return multiple matches in excel Question: Unfortunately, I can't make enough sense of […]

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

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all these are single conditions -- you can't pass multiple conditions, say for example: USA or China or France in column_countries […]

In a previous post I created unqiue distinct column headers (A20:C20) and then categorized adjacent cell values into each column (vba). In this post I´ll show you how to do the same using only excel formulas. Create unique distinct column […]

By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel (See comments). I have no clue if that is true or not but my intention now is to develop that […]

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

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array formula in B23: =INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER copied down as far as […]

Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for more than 2 courses? Answer: Array formula in B25: =INDEX(tbl, SMALL(IF((COUNTIF(tbl, tbl)>=3)*(ISTEXT(tbl))*NOT((COUNTIF($B$24:B24, tbl))), ROW(tbl)-MIN(ROW(tbl))+1, ""), 1), (SMALL(IF((COUNTIF(tbl, tbl)>=3)*(ISTEXT(tbl))*NOT((COUNTIF($B$24:B24, tbl))), ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1, […]

Array formula in D12 (cell references): =INDEX($D$4:$D$8, SMALL(IF(($B$4:$B$8<$D$10)*($C$4:$C$8>$D$10), ROW($D$4:$D$8)-MIN(ROW($D$4:$D$8))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER copied down as far as needed. Array formula in D12 (named ranges): =INDEX(Value_col, SMALL(IF((Rng_1<Inp_val)*(Rng_2>Inp_val), ROW(Value_col)-MIN(ROW(Value_col))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER […]

Question: Hi, What type of formula could be used if you weren't using a date range and your data was not concatenated? ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 […]

Question: Does anyone know how to do a vlookup of three columns to pull a single record? Answer: I am guessing that you want to lookup a value in any of three columns to pull a single record. In this […]

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, etc.) with related parties but seldom send payments. Once in a while, I would like to reduce the number of […]

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from the list A1:A11 in C1. How do I find those summed numbers in C1? I am going to use Excel […]

In a previous blog post Sum adjacent values using multiple lookup text values in a column in excel I created a formula to sum specific values using two or more conditions in a column. In this blog post I will […]

In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula to lookup values in range using one criterion and return (if possible) multiple adjacent values. In this blog post I´ll […]

Here is a follow up to this previous post: Vlookup with 2 or more lookup criteria and return multiple matches in excel Sum search criteria price values Cell references in array formula in B14: =SUM(IF(COUNTIF(B10:B11, B3:B7)>0, C3:C7)) + CTRL + […]

In previous posts I provided formulas on how to lookup one value in a list and return multiple matches. Using array formula to look up multiple values in a list How to return multiple values using vlookup in excel Search […]

Filter records between 13 and 16 can also be done using Advanced filter or in Excel 2007 creating a table. Array formula in A19: =INDEX($A$2:$D$9, SMALL(IF(($D$2:$D$9<=$B$14)*($D$2:$D$9>=$B$13), ROW($D$2:$D$9)-MIN(ROW($D$2:$D$9))+1), ROW(1:1)), COLUMN(A1:A1)) + CTRL + SHIFT + ENTER copied right to D19 and […]

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download excel file Lookup all values and find max date Lookup and find last date using multiple conditions Lookup and find […]

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is in cell B19 (Pen). Array formula in C19: =SUM(IF(B4:F14=B19, C4:G14, 0)) + CTRL + SHIFT + ENTER Download excel file […]

Question: can you please modify the function (f2:f11) to display the text strings in CELL G.. disregard the ROW NUMBER Display thnx the function will be in data validation.. and it will display 2 outputs in the list but if […]

Question: Hi, The formula here works great but I can't figure out how to change it to work with data in columns. Here is what I have: =INDEX(A2:E2,SMALL(IF(A1:E1=A3,COLUMN(A1:E1),""),COLUMN())) A B C D E 1 A B A C D 2 […]

Question: I have a range of dates and values. See picture below. I would like to identify the largest value where the adjacent date to the left is the year 2009 and the month is November? Answer: I have colored […]

Extracting the largest value within the date range specified in C3 and E3 Array formula in C5: =MAX(IF((Date_col<=$E$3)*(Date_col>=$C$3), Close_col, "")) + CTRL + SHIFT + ENTER Extracting the smallest value within the date range specified in C3 and E3 Array […]

Array formula in B19: =INDEX(Product, SMALL(IF(($C$11<=Price)*($C$12>=Price)*(OrderDate<=$C$16)*(OrderDate>=$C$15), ROW(Product)-MIN(ROW(Product))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as needed. Named ranges Price (B3:B6) OrderDate (C3:C6) Product (D3:D6) What is named ranges? Download excel file for this tutorial. Lookup two […]

Array formula in B14: =INDEX(Product, SMALL(IF(($C$10=ID_num)*(OrderDate=$C$11), ROW(Product)-MIN(ROW(Product))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as necessary. Named ranges ID_num (B3:B6) OrderDate (C3:C6) Product (D3:D6) What is named ranges? Download excel file for this tutorial. Lookup two […]

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula #2 in B16: =INDEX(D3:D6, MIN(IF((C10=B3:B6)*(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1))) Alternative array formula #3 in B17: =INDEX(D3:D6, MATCH(1,COUNTIFS(C10,B3:B6,C11,C3:C6),0)) Alternative array formula #4 in B18: […]

The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to […]

If you can rearrange your data, read this post: Lookup values in a range using two or more criteria and return multiple matches in excel, part 2 You can use a lot easier array formula if you rearrange your data. […]

Array formula in B26: =INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), MATCH(0, ISNONTEXT(INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), , 1))+COUNTIF($B$25:B25, INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), , 1))+(COUNTIF(tbl_2, INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), , 1))=0), 0)) + CTRL + SHIFT + ENTER copied down […]

Array formula in B15: =INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)=1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(0, ISNONTEXT(INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)=1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)=1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))<>1), 0), 1) + CTRL + SHIFT + ENTER Named ranges tbl (B5:F11) What is named ranges? […]

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 22 A3 data2 B3 55 A4 data3 B4 44 A5 data5 B5 22 A6 data4 B6 33 A7 data9 B7 […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) customer#, cust name, appt date, appt time, venue, coordinator, assistant A question asked in this post: Lookup with multiple criteria […]

This blog post describes how to filter text values and then numbers from a range. Array formula in B15: =INDEX(tbl, SMALL(IF(ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1), ROWS(B14:$B$14)), MATCH(SMALL(IF(ISTEXT(tbl), (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384, ""), ROWS(B14:$B$14)), SMALL(IF(ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1), ROWS(B14:$B$14))+((COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384), 0)) + CTRL + SHIFT + ENTER copied down as […]

Question: How would i change the formula, if the "ITEM" is in column A, the "VALUE" is in column B and the "COMPANY" is in column C? and what is i have another column "NAME" in between columns and ITEM […]

Array formula in B2: =IF(ROWS(B2:$B$2)>ROWS(List), "", INDEX(List, MATCH(LARGE(IF(MAX(LEN(List))=LEN(List), (LEN(List)&","&ROW(List))*1, ""), ROWS(B2:$B$2)), IF(MAX(LEN(List))=LEN(List), (LEN(List)&","&ROW(List))*1, ""), 0))) + CTRL + SHIFT + ENTER copied down as far as needed. Named ranges List (A2:A19) What is named ranges? How to customize the formula […]

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: I need to do exactly the same thing, but with three character alpha codes instead of numbers. Can anyone help? […]

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list of strings in column D was to increase to a large number e.g. 15, how would you tell excell to […]

Question: How do I search and display a range of values, if the values were numerical, say 1 - 40 could I set a range of 20 - 25 (either in one or 2 cells) and get 6 values (inclusive)? […]

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle and last name column could I only display the values in which all three cases are true? This blog article […]

Question: How do I filter values between 0.5$ and 1.5$ from two columns using excel array formula? Answer: Array formula in A12: =IFERROR(INDEX(List1, SMALL(IF((Price1<1,5)*(Price1>0,5), ROW(Price1)-MIN(ROW(Price1))+1), ROWS($A$12:A12))), INDEX(List2, SMALL(IF((Price2<1,5)*(Price2>0,5), ROW(Price2)-MIN(ROW(Price2))+1), ROWS($A$12:A12)-SUM(IF((Price1<1,5)*(Price1>0,5), 1, 0))))) + CTRL + SHIFT + ENTER copied down […]

Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in A2: =MIN(IF(COUNTIF(List, List)>1, List, ""))=A2 How to implement conditional formatting formula to your workbook in excel 2007 For example, your […]

Question: How do I count the number of times a text string exists in a column? Answer: Array formula in cell B11: =(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9) How to create an array formula Copy array formula (Ctrl + c) Select cell B11 […]

This blog article is one out of five articles on the same subject. Search for a cell value in an excel table Lookup with multiple criteria and display multiple search results using excel formula Lookup with multiple criteria and display […]

Question: How would I go about looking up data in an cross reference table. I have the header row (i.e. 24) value and the column (mm) value and want to return the x/y value. i.e I have 25/X and 9/Y […]

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns but they both have to be somewhere on the same row to match. The search is not case sensitive. Answer: […]

Question: How do I search a list containing First name column and a last name column? I want to search both columns at the same time with two different criteria (one criteria for each column) and then display multiple search […]

Question: How do I highlight dates that meet a criteria using conditional formatting? Table of contents Highlight values in a column Highlight values in a row Highlight records Sort values Highlight values in a column How to apply the conditional […]

Question: How do I search a list for two text strings and return a list with where both strings occurs? Answer: Here is a formula (F2:F4) that creates a list with text values where both strings occur and is not […]

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow cells are input cells. The gray area is the sorted value list. Array formula in G4:G17: =IF(COUNT(SMALL(IF(($A$4:$A$17<E4)*($A$4:$A$17>D4), VALUE((B4:B17)), ""),ROW()-3))>=ROW()-3, SMALL(IF(($A$4:$A$17<E4)*($A$4:$A$17>D4), […]

Question: How do I find the number of occurances a word exists in a range of cells? It does not have to be a exact match but case sensitive. Column A1:A15 is the cell range. Answer: Cell D1 is the […]

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array formulas (dynamic) Filter rows using excel table Filter rows using excel table and vba (dynamic) Filter rows using array formulas […]

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter an array formula Select cell B1 Copy above formula (Ctrl + c) Click in formula bar Paste formula (Ctrl + […]

Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search for a text string in a data set. Cells containing the text string are returned in cell range C10:C20. Example, […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with a criterion Find closest value Question: How to find closest number in a list? Answer: Array formula in cell C2: […]

Problem: Values between 0 and 4 returns Small. Values between 5 and 9 returns Medium. Values between 10 and 15 returns Large? Solution: Formula in cell B1: =LOOKUP(A1,{0;5;10},{"SMALL";"MEDIUM";"LARGE"}) LOOKUP(lookup_value,array) Looks up a value either from one-row or one-column range or […]

Question: How to find the latest date in a list and the adjacent value? Answer: To find the latest date, type =MAX(A:A) in a cell (C1). To find the adjacent value, type =VLOOKUP(MAX(A1:A26),A1:B26,2,FALSE) in a cell (C2). Download excel sample […]