# Two Criteria - Multipule Matches That Are The Same - 1 Answer To Be Return

Oct 16, 2008

I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close: =SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)). but this adds up all the matching results.

What I need to do is match the info in columns A and B and return a number in column C. The problem is that there are multiple rows with identical entries and I just want to return one of them. So I was thinking a version of the above that produced a mode or average would give the desired result.

## Return Row Number That Matches 2 Criteria

Feb 15, 2010

I have a produced an Excel workbook which uses a VBA sign in/out userform.
Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.

To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8.
I need to have the row number returned for the IDnumber where the Out cell is blank.
This should be the last occurrance of the IDnumber

Example
Row
1 ID# In Out
2 1 9:00 13:00
3 4 9:00 13:00
4 3 9:00 13:00
5 2 9:00 13:00
6 5 9:00 13:00
7 6 9:00 13:00
8 2 14:00

## VBA - Sum A Range Once Meeting Multipule Criteria

May 11, 2008

I have been having problems all morning and have not been able to solve it!

I am sure this is very simple....

What I would like to do is assign the result of a sum to a variable. This part is easy what I cannot fathom or find any help with is the multipule criteria bit!

Here is the detail.
Column A - Employee Number
Column C - Area
Column F - Type
Column D - Amount paid.

I would like to sum the amount paid based on the 3 criteria, Employee Number, area & type and assign this to a variable to be used later in a sub I'm playing with.

## Cross Check 2 Ranges For Matches & Return Row Number Of Matches

Apr 29, 2009

I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK

## Return Matches & Non-Matches From Delimited Cells Against List Range

Jan 29, 2010

In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":

1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List

2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List

3.) Report In Column D: Identify and return all unique values in "Column C" as a List.

Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"

## Return Value From 2nd Column When Value In 1st Column Matches Given Criteria

Oct 4, 2008

I have a column containing text values eg M1, T2, M3, A4 etc. and I am trying to return the value (numeric) in a second column to a cell when the text value in the first column is matched.

## Cell Return All Matches From A List And Have The List Of Matches Reduce As You Type

Jan 28, 2009

is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?

## Return Answer Dependant On /

Apr 5, 2009

in cell d1 i have

hk/q46559

in cell i1 i need to return whatever is before the "/"
in this case hk

and in cell i2 i need to return the number after the q
in this case 46559

## Return Answer Based On Cell Value

Aug 29, 2009

im looking to return an answer in cell c28. in b28 it will say "best goalkeeper"
in c28 i need a formula to look in range d2:d6 then return the highest answer and correspond that to whats in b2: b6. so we could say

(b2)shilton(d2) 33
(b3)corrigan (d3) 55
(b4)parkes (d4) 66
(b5)schmeichel (d5) 100
(b6)cudicini (d6) 14

in c28 it will return the answer schmeichel because he is the highest
and in d28 it would return the points he has.

## Formula That Will Check Multiple Criteria Before Returning Answer?

May 14, 2014

Attached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.

## Excel 2010 :: SUMPRODUCT Answer To Return Blank When No Data In Specific Cells

May 12, 2013

I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.

Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6

Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0

The formula I am using is:

=(SUMPRODUCT(\$A\$6:\$A\$15,C6:C15))/\$A5

Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.

Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.

My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?

## Formula To Find Matches And Return Yes Or No

Jun 11, 2014

In the attached sample work book Col E has text that I want to check if it is also in Col G and return Yes or No into Col F

## Return A List Of Unique Matches

Nov 27, 2006

I need to return all the unique matches from a set of data.

For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.

Example of data set I need to lookup in:
prod 1 venue1
prod 2 venue 2
prod 1 venue 3
prod 1 venue 10
prod 2 venue 6

I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.

It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....

## Identify Matches And Return Values

Nov 10, 2009

1) Check to see if a value in column A of worksheet A appears in column A on worksheet B.

2) If it DOES, then display the value from the same row from column B on worksheet B, in column B on worksheet A.

3) If it DOESN'T, display the value "EACH" in column B on worksheet A.

e.g...........

## Search And Return Address For All Matches

Feb 14, 2010

I'm looking for a Formula in order to retrieve all addresses of all matches equal to the search criteria located in cell: E2

The results are hand-typed in col. G

This is a case of a TWO-DIMENSIONAL "Arbitrary Lookup" Arbitrary.

The range A1:C10 was named: TBL and the formula should refer to TBL as it can be dynamic in size.

PS: can anybody tell me why I don't get any E-Mails when an answer is posted?

## How To Return Exact Matches Only Using VLookup

Feb 13, 2007

I have the following formula which, if text is in A1, will lookup from a list of worker's comp codes to locate the code in A2 and return the wording for that
=IF(ISTEXT(A\$1),VLOOKUP(A\$2,Codes!\$B\$3:\$C\$720,2),"")

However, if there is not an exact match to the code in A2, it returns the wording for the next closest code.

Is there a way to make it so that it will only return the wording for the exact match and return "invalid code" if the number in A2 is not found on the list on the "Codes" sheet?

## Compare 2 Columns And Return Matches

Aug 12, 2008

I have two columns, one with the target text (D:D) and one with text I want to compare it too (A:A). If the text in column (D:D) matches the one in (A:A), I want to return the value in (B:B). I attached a sample of the problem i'm dealing with.

## Worksheet That In Column A Has Multipule Row

Jun 28, 2007

i have a worksheet that in column A has multipule row that begins with:
"Subtotal By "

What i would like to do is find each of these rows and copy the data to another sheet
all the "Subtotal By " are grouped together but could be located in different areas of the workbook.

i have attached a file with an example.

## VB Copy Multipule Cells

Mar 27, 2008

I have data on sheet2 that needs to be copied to sheet1 when i run the vb script. It also does a load of other things so would like to keep the script a s short as possible.

Curently i run this for each cell i want to copy:

## Multiple Criteria Matches

Mar 16, 2007

I am trying to set up a worksheet whereby two critera when matched from a drop down list will populate cell 'x' with a coressponding answer. if you have Destinations then a From and To column with various locations listed below, then in a thrid column you have an output cell which is kilometres. What formula can i use to match the various location 'to' and 'from''s with the correct kilometre match?

## Return Value If Number Is In Range Even And Then Matches The Street

Mar 16, 2014

Basically, I have a huge list of addresses with ID numbers, and a list without the ID numbers. An example would be;

I need to identify if the address is on the street, is in that block of flats (i.e between 2-10, and even number), and then return the ID.

I've already split the number between two cells, but then its all gone a bit wrong.

## Compare Column In Different Workbooks & Return Corresponding Value Of Matches

Aug 7, 2008

I have two separate workbooks, each with a great deal of information, such as name, ssn, dates, statuses, etc. I would like to compare the SSN column for both workbooks, and when it finds a match (which there would be many) to copy the MOS cell from the same row to the other workbook. Here is my breakdown with names:

Workbook 1: Macros.xls
Workbook 2: MASS 162.xls
The SSN column is column C in both workbooks
When there is a match found between the SSN column in Macros.xls and the SSN column in MASS 162.xls, I need to copy the MOS cell (column E) from the MACROS.xls workbook to the MASS 162.xls workbook into the same row, but in column M, which is currently empty.

## Replace Column With Certain Criteria Matches

Apr 20, 2009

4 columns a-d. now if i find either 1 of the 2 particular strings "hello" or "bye" in column A, I want to move column b into its place and leave the rest of the columns untouched

Example:

dog, circle, blue, 123
hello, frog, green,223
pig, triangle, red, 445
bye, elephant, grey, 322

into

dog, circle, blue, 123
frog, blank, green, 223
pig, triangle, red, 445
elephant, blank, grey, 322

## Finding Last Row In Data Row That Matches Criteria

Oct 14, 2009

have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.

## Userform For Search That Matches Three Criteria?

Mar 18, 2013

I created a userform for a giant spreadsheet with tons of data in it. There are three criteria. I created two command buttons - one that's "search" and one that's "close." The close works fine, but I don't know enough to program the "search" command. I'd like it to pull all data that matches the three criteria - rows H, D and M. So, if you answer all three, the macro will pull the rows of data that matches and paste it into another workbook (that opens in another window).

I'm not sure userforms are typically used for this sort of thing...

## Identify Matches Between 2 Lists Using Criteria

Oct 12, 2009

the attached workbook. Am trying to write a formula in Column F, which returns the results shown in Column H. Hope the workbook clearly sets out the criteria I'm trying to apply to the Lookup.

## Find Matches In Multiple Columns And Return Header

Oct 29, 2012

Lets see if I can put this into words.

I am trying to find matches of a specific cell in various columns. Example:

-ABC123-----------abc123--------------abd123
-abd123-----------hjk321---------------hdn234
-Abc123-----------dsd123--------------sds332

If I searched for the value "abc123" I want it to return Headers 1 and 2 in a seperate column. It would not matter if the same value is in one column multiple times

So the results would show me the Column Heading for anything that reads: "abc123", "ABC123", "AbC123", "aBC123"

Is this possible?

## Lookup Function Required To Return Multiple Matches

Apr 24, 2007

I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...

My column headings are as follows...

In column A (type) would be the type of contact eg:business/personal/family etc.

So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense!
There are over 1000 records i need excel to search through & return matches.

I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.

## Compare Columns On Different Sheets, Return Matches To Third Sheet

Mar 21, 2007

I've got two worksheets ("June" & "July"). On both worksheets, column A is comprised of ID numbers and column B contains dollar amounts. I need to compare the ID numbers in Column A on each worksheet, and if they match I want to copy the ID number and the amount to a third worksheet ("Results").

## Search Range, Return Adjacent Cells Of Matches

Jun 21, 2007

I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).

Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:

Sheet1
cup Y
bowl Y
spoon
fork
knife Y

Sheet2

cup
bowl
knife

..not..

cup
bowl
knife