# Search Multiple Criteria If Match Append Multiple Cells To One Cell?

Apr 28, 2012

I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!

I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:

Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.

COLUMN ACOLUMN BCOLUMN
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty

I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.

Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.

After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:

COLUMN ACOLUMN BCOLUMN
ArkansasLife Health11114ArkansasLife Health

## How To Use Match And Multiple Criteria To Fill Multiple Cells

Nov 21, 2012

I am using this formula but I don't know how to get it to populate more than one cell.

Here is the formula:

=INDEX(APPROVED!\$A\$3:\$A\$1000,MATCH(\$F\$4&\$H\$1,APPROVED!\$D\$3:\$D\$1000&APPROVED!\$C\$3:\$C\$1000,0))

It has multiple matches but I can only see the first match in the cell.

## Return Multiple Values That Match Single Search Criteria?

Nov 28, 2012

Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.

I would like to be able to get a list of all values in a given column that match a specific name in another column.

Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.

For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.

The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.

CPT Sample Book.xlsx

## Search Multiple Values In A Cell With Multiple Criteria

May 22, 2009

I am trying to search for multiple values in a cell with Multiple criteria.

E.g: Please find attached the sample excel data.

I tried using VLOOKUP which is not working as I am having multiple values in column A.

## Match Value Of 1 Cell To Multiple Cells And Give Multiple Values

Oct 7, 2006

Sheet 2 is the problem.

I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.

Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.

I have included a sample.

## Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:

TABLE 1:

Project
Benefit Type
Delivered or Enabled
Benefit

PJ1
Financial
Delivered
Saving of \$4M over 24 months.

[code]....

I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.

TABLE 2:

Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled

[code]....

So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

## Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?

Feb 1, 2012

Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)

e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated

Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.

e.g.
AAA blue round elongated
BBB orange
CCC smelly

## MATCH For Multiple Criteria While Ignoring Empty Array Cells

Nov 11, 2009

I'm working with a large amount of data (A21:BZ1503) and I'm trying to identify unique situations where any pre-defined combination of multiple columns in one row is flagged by producing a pre-defined value. For example:

I have my pre-defined criteria in worksheet 'X' hidden in my workbook -- note that there are many blank cells.

On worksheet 'A' the various users enter data -- each row is a unique group with data entered into the columns -- again note a cell can be blank:

## Search Workbook For Cells That Meet Multiple Criteria And Return Results From Rows

Jun 28, 2013

I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.

Create_List.xlsx

## Index Match - Multiple Criteria And Multiple Matches

Dec 2, 2013

I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.

The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).

And I want it to extract "Bills" value from "rule" sheet.

There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.

How do i solve this problem with index-match function?

## Search Multiple Worksheets Against List Of Non-Exact Search Criteria?

Nov 2, 2009

I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:

1. The rows containing the matched search criteria in the first sheet are highlighted.

2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.

I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.

## Append Multiple Workbooks With Multiple Worksheets

Sep 5, 2008

I would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.

## Merge Multiple Worksheets By Append With Same Cell Structure To One Sheet

May 10, 2007

I have a workbook that has a lot of worksheets. I want to merge all the worksheet data, skipping blank rows from each worksheet and simply appended the data on one single worksheet. The goals is to just copy the data from worksheet b and append to worksheet a>copy data from worksheet c and append to worksheet a> etc. etc.

## Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$R1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$E1">"'"&\$H\$1:\$H\$43&"'!\$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$S1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$G1">"'"&\$H\$1:\$H\$43&"'!\$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$T1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$I1">"'"&\$H\$1:\$H\$43&"'!\$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$U1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$K1">"'"&\$H\$1:\$H\$43&"'!\$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- \$H\$1:\$H\$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.

## Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?

Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.

CURRENT SHEET

89PENS ABC STATIONERY CO LLC [code]....

The attached example file may be a better alternative to view this problem.

## Search For Multiple Text Strings In A Cell - Return One Of Multiple Corresponding But Different String

Mar 11, 2014

I have a spreadsheet with 1,000 rows of data.

Each Cell in Column A has a different long text string.

I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"

E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.

Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?

## How To Use Index Match To Search For Value From Multiple Sheets

Sep 3, 2012

I have a set of values in column A in sheet 1 and a compilation of some of the same set of data in column A in sheet 2. Using the index match function, I have been able to list the corresponding values in column B of sheet 2 onto column B of sheet 1 (for those values which are common to both sheets - matching occurs between column A in both sheets). However, I now have some of the set of data in sheet 2 and the rest in sheet 3 (again, in column A). Is there any way of using index match to search in both sheets and index the corresponding value from column B of the sheet containing the matched value in column B of sheet 1?

I have tried this by nesting the index match functions for each sheet into two separate IF arguments but haven't had any luck. Is there a better way of doing this?

This is what I have currently tried in Cell B2 of sheet 1:

=IF(INDEX(Sheet2!\$B\$2:\$B\$3001,MATCH(A2,Sheet2!\$A\$2:\$A\$3001,FALSE),1),IF(INDEX(Sheet3!\$B\$2:\$B\$3001,MATCH(A2,Sheet3!\$A\$2:\$ A\$3001,FALSE),1)))

Excel keeps saying this formula has errors but I have no idea as to what changes I need to make to get the formula to model my situation. Then again, I'm not even sure if this is the approach I should be taking to create a formula that models my situation.

## Search For Multiple Criteria

Feb 14, 2010

I have a spreadsheet with a database search function incorporated. Whilst this works fine, I was wondering if there is a way to combine the current single query searches into one search macro. I have setup a userform called CriteriaSearch that I would like to use to search my database tab. An example search would be:

User requires someone who speaks French (5), is female, has a rank of 3, is available immediately and has notes/keywords/keyphrases of "excellent linguistic and cultural knowledge". The appropriate fields on the userform are populated accordingly. The user selects search and a macro runs to find matching records. To make things slightly easier, the criteria (Language, Gender, Rank etc) all have fixed column references.

## Sum Of Multiple Cells Within Multiple Sheets With Varying Criteria

May 5, 2013

I have a spreadsheet that has about 50 sheets and a summary sheet at the front the layout of the summary sheet is column A has skill sets from A4 to A20 and across the top it has week commencing dates from F3 to about BA3 (dates go from w/c 06/05/13 to 08/12/14). On the other sheets they have the dates in the same columns but the skill sets vary as to each project, some projects may have two of the same skill set in column A because there is two people needed who can do the same thing, I need to total up the days over the sheets for each week and skill set.

I have been using the SUMIF formula as follows:

=SUMIF('Sheet2'!A8:A10,'Summary'!A4,'Sheet2'!F8:F10)+SUMIF('Sheet3'!A8:A15,'Summary'!A4,'Sheet3'!F8:F15)

And so on for each sheet and then change the formula for the next skill set and then again for the next column.

Is there a way of doing this without having to enter a formula into each cell, at the moment I am coping and pasting the formula into word to do a find and replace to change the SummaryA4 to SummaryA5 etc and then into the next cell for the corresponding date.

So to make this seem like it makes any sense I am looking for say an 'Administrator' in all the project sheets over all the dates and for it to add it up for the summary sheet. Under the corresponding week against the skill set.

So it would be F4:F20 for w/c 06/05/13, G4:G20 for w/c 13/05/13 and so on until all skill sets.

## Select Multiple Cells Based On Multiple Criteria?

Sep 25, 2013

I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.

In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.

I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.

## Search Multiple Workbooks & Match Named Ranges

Dec 6, 2006

I need to create a macro to find matches between multiple specific workbooks and a named range. I am new to macros and am very confused. After searching the forums here, I am still lost, even though they are very informative. So far,I have created a Dynamic Named Range called NamesList

=OFFSET(CurrentDay!\$B\$2,0,0,MATCH("*",CurrentDay!\$B:\$B,-1),1)

that selects the cells which I update manually each day. Once I have updated the list of names, I need to create a macro that will decide whether or not the names in the dynamic named range CurrentDay (located in workbookA) already exist in workbookB. The macro needs to create a list of the which names already exist in the workbook, and which do not. For the names that do not exist in workbookB, I then need to search workbookB and workbookC for matches, again creating a list of results. Until now I have been manually using Ctrl + F (Find Method)and going down my list of names to search through each workbook.

## Search Multiple Criteria For Corresponding Data?

Feb 26, 2014

I am looking to search in a table (say 4 columns) corresponding to multiple criterion (one for every column except fourth) and returning the values which are numerous (from column 4). I have tried the INDEX function but it only gives me one of the many cells. I am working on a table with +20000 cells per column

## Search For Duplicate Using Multiple Criteria

Jun 21, 2013

I need a formula that will tell me what Tracking # in the spreadsheet has multiple dispositions and what those disposition are. I need the formula to use column and cell coordinates and not using word searches.

example

Tracking # Req.# Disposition
1490069 045 snakes
1015907 047 candy bars
1488026 044 chickens
1015907 047 sand paper
1015907 001 fruitcups
1490129 046 cupcakes
1484817 043 Cats and dogs

## 2 Search Criteria But Multiple Display?

Oct 5, 2011

I want to display multiple results based on 2 search parameters, where by when I enter flight number and Dep. Airport it matches the trip numbers and dispalys all the rows matching all flight number and Dep. airport based on the unique trip number.

Notice: - one trip number could consist of multi flight sectors.

1) Explanation of the data :

MMM1805 = flight Number
935 = dep. time
\$AS41 = registration code

[Code].....

## Search Criteria With Multiple Answers

Apr 9, 2012

On my spreadsheet i want to find the results from 2 criteria that i entered.

My search criteria are "Oostbos" and "AA8", and excel has to find this from another spreadsheet that i made for rostering.

OostbosN3 evelineAA8N3 evelineAA8N2 MargaAA7

The problem is that i have multiple shifts with the "AA8" criteria, but my function only finds the first one.

I used the following function:

Code:
=IF(C8="";"";INDEX('Afdeling PG PH'!\$A\$6:\$A\$28;MATCH(C8&\$A\$7;'Afdeling PG PH'!\$B\$6:\$B\$20&'Afdeling PG PH'!\$AI\$6:\$AI\$20;0)))

Also when the AA8 cel is empty that i doesn't show anything.

How the second N3 eveline, shows the 2nd result and so on.

## Search Based On Multiple Criteria

Aug 29, 2013

An example of my data is:

Column A Column B Column C

I need a formula that checks both column A for text (ABC) and column B for text (JKL, two different criteria) and give 'Answer 3'. There will a lot of data and it will not always be the bottom answer.

## Search Multiple Sheets For Criteria In Col A?

Oct 30, 2013

I have thought about recording a vlookup but this might be messy and prone to errors. Is there anyway to search through multiple sheets for a value (code) in Col A sheet 1. And return X in sheet 1 in the next column.

## Use Multiple MATCH Criteria?

Jan 19, 2010

I have been trying for the last 2 hours to write a formula that does the following;
If a number appears in this table (on another worksheet named VAT Codes) then return VAT but if it appears in this table (on another worksheet named VAT Codes) then return NO VAT. The following formula returns VAT but N/A when it should say NO VAT; =IF(F3="","",IF(MATCH(F3,'VAT Codes'!\$I\$7:\$I\$19,FALSE),"VAT",IF(MATCH(F3,'VAT Codes'!\$K\$7:\$K\$143,FALSE),"NO VAT","")))

## Copy & Append To Report, Rows From Multiple Sheets If Any Cell Meets Format Color

May 1, 2008

I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet.

On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.

## Return Multiple Values Using Three Search Criteria?

Jun 9, 2014

I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F\$9 (see below) is giving me an error

=IF(ROWS(A\$9:A9)>\$A\$5,"",INDEX(Data!A\$2:A\$387,SMALL(IF((Data!\$A\$2:\$A\$320>=\$A\$2)*(Data!\$A\$2:\$A\$320<=\$B\$2)*(Data!\$B\$2:\$B\$320=\$C\$2),ROW(Data!\$A\$2:\$A\$334)-ROW(Data!\$A\$2)+1),ROWS(A\$9:A9))))

test1.xlsx