Lookup And Return Multiple

Jan 30, 2009

I have a spreadsheet similiar to the below:

COLUMN A.....COLUMN B
Person 1.......Process 1
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 3
Person 2.......Process 1
Person 2.......Process 4
Person 2.......Process 1

and so on.

In the list, the "People" aren't neccessarily in order, for example, later in the list, Person 1 appears again. There are a total of 10 processes, and any person can do any number of each.

What I ideally want is in Columns D, E, and F, is something like the following:

COLUMN D.....COLUMN E....COLUMN F
Person 1...........3.............Process 1
Person 1...........2.............Process 2
Person 1...........1.............Process 3
Person 2...........2.............Process 1

so a summary of the main data, showing how many each process each person has got.

I have, and failed miserably, getting arrays to work but that might just be down to my own incompetence. I have researched into Countif's and Lookups, but it's not quite working.

The data in columns D, E, and F needs to be fairly compact so ideally I wouldnt want any spare rows between the end of the summary data about Person 1 and the start of Person's 2 details.

View 9 Replies


ADVERTISEMENT

Lookup Multiple Same Value And Return Multiple Corresponding Value In Ascending Order

Oct 9, 2008

I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.

What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.

Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.

View 3 Replies View Related

Lookup And Return Multiple Results

Mar 31, 2014

I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.

View 10 Replies View Related

Lookup Value In List And Return Multiple Corresponding?

Apr 14, 2014

How to write formula of following condition:

Input date on left column and the cell on right will automatically look up this date from the list located in other spreadsheet and return multiple corresponding in sequence order? Once another date is entered on left column, it will do the same function again on right column?

View 5 Replies View Related

Lookup Value And Return Multiple Results

May 4, 2014

I have been using this formula to do lookup and return values

"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"

I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.

Is there a way to stop it from changing the value once a value is entered in a corresponding cell?

Link to the original formula thread. [URL] ........

View 10 Replies View Related

Lookup To Return Multiple Values

Nov 11, 2008

Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".

View 2 Replies View Related

Lookup Multiple Columns Return A Value

Jun 26, 2012

Here are two sheets:

Sheet1
systemip1 ip2 ip3 ip4 ip5 ip6
system11.1.1.11.1.1.21.1.1.31.1.1.41.1.1.51.1.1.6
system22.2.2.22.2.2.32.2.2.42.2.2.52.2.2.62.2.2.7
system33.3.3.13.3.3.23.3.3.33.3.3.43.3.3.53.3.3.6

Sheet2
ip system
1.1.1.3
2.2.2.3
3.3.3.6
3.3.3.1

Sheet 1 has 7 columns(system,ip1,ip2,ip3,ip4,ip5,ip6 and ip7)
Sheet 2 has 2 columns (ip,system)

I have to fill column "system" in sheet 2 with "system" listed in column 1 of Sheet1.

In other words look for "ip" in Sheet2 in 6 columns of Sheet1 and return column 1 of sheet1 as value.

View 5 Replies View Related

Lookup With Multiple Result Return

Aug 17, 2007

I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.

View 9 Replies View Related

Multiple Lookup S- Return A Value From Table

Jul 22, 2009

I am trying to return a value from table below (column 4) based on values in the 1st 3 columns. I have data with the 1st 3 columns but need to lookup each and return the 4th so I have tried some formula's with if and vlookup statements but keep getting lost....

View 9 Replies View Related

Lookup Multiple Columns And Return Top Row

Sep 21, 2006

I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.

View 9 Replies View Related

Lookup & Return Multiple Items

Dec 21, 2006

I am trying to make a search form in Excel 2003 where I can lookup data for a specific value which may have multiple rows. I have several columns, column A Tracking Number, B Order number, C Item Number, etc.

For example; Order number S6001 has three item numbers on rows 1,2,and 3.

I can get the data on row 1 using the basic "LOOKUP" function but not rows 2 and 3. How do I have all of the line items (rows) returned for that order number.

View 3 Replies View Related

Lookup & Return Multiple Occurrences

Jul 18, 2007

In Sheet1 are unique VM entries (Column A) which have a Group (Column B), Time (Column C) and a Reference (Column D) which can be same for other VM entries. In Sheet2 are the unique Reference Entries (Column A) which need Group (Column B) and Time (Column C) entries populated from Sheet 1. Multiple occureneces need to go in one cell, I realise this is not the best idea, but for this purpose they need to be and seperated by a comma or semi-colon.

In B2 I've used this formula:
=INDEX(Sheet1!A1:D11,MATCH(A2,Sheet1!D1:D11,0),2)
Which partly works, but only returns the first occurrence of H1 on Sheet1. I need all occurrences (i.e G1;G4;G8) returning for B2.

I can only think that some VBA script may be needed but I'm not really an expert in this area. In the full spreadsheet there are hundreds of rows and multiple entries, where Group and Time need to be populated on the second sheet

View 3 Replies View Related

Lookup Formula To Return Multiple Results?

Dec 8, 2013

I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.

For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.

I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")

I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.

View 3 Replies View Related

Lookup To Return Multiple Cell Values?

Jan 20, 2014

I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.

View 8 Replies View Related

VLOOKUP To Return Multiple Cells Of Same Lookup Value

Dec 1, 2009

I am trying to create an automated receipt for a list of artists. I have given the artist an individual reference so I am doing a VLOOKUP to return description/price value etc but I can only get it to return the initial value and I have approx 10-15 works under each reference. Maybe VLOOKUP is the wrong formula to use.. I have attached the file.

View 5 Replies View Related

Array Lookup, Return/Sum Multiple Values

Jan 18, 2010

In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.

View 3 Replies View Related

Lookup Multiple Conditions To Return One Result

Sep 28, 2009

I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.

So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.

etc.

I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.

View 3 Replies View Related

How To Return The Maximum Value In A Multiple Results LOOKUP

Dec 11, 2008

What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.

Please see attachment.

Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)

View 6 Replies View Related

Return Multiple Values From Lookup Table

Jan 5, 2007

the formular that i have in the attached file is returing all the customer numbers
Is it possible to return only one value of each
the data is in the "data" sheet and the formulars are in sheet1

View 3 Replies View Related

Lookup Multiple Criteria In Different Tabs To Return Result

Jun 5, 2008

I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.

File is attached.

View 9 Replies View Related

Lookup Multiple Arrays Return Text Or Numeric?

Jan 23, 2012

Is there a formula to look in multiple arrays and return whatever it finds i.e. text or numeric values?

Sheet1  ABCDEFGH1Cat0 CatFive Donkey32   Mouse2 Wolf43 
4WolfFour CatFive Donkey35   Mouse2 WolfFourSpreadsheet FormulasCellFormulaB1=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(A1,$D$1:$E$2,2,0),
VLOOKUP(A1,$G$1:$H$2,2,0)))B4=LOOKUP(REPT("z",255),CHOOSE({1,2,3},0,
VLOOKUP(A4,$D$4:$E$5,2,0),VLOOKUP(A4,$G$4:$H$5,2,0)))

View 9 Replies View Related

Return Values Using Lookup Value From One Sheet Across Multiple Columns

Dec 11, 2012

I'm trying to find a way to:

Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"

Things to note:

a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.

b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".

If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".

If that value does not occur in sheet B, the corresponding cells should remain blank.

If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.

View 2 Replies View Related

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...
Type Region Contact Phone Email ADD1 ADD2 ADD3

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.

View 9 Replies View Related

Lookup Single Value In One Array, And Return Multiple Values

May 30, 2006

I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.

View 9 Replies View Related

Return Multiple Values From Lookup To Single Cell

Feb 20, 2007

i would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2

View 8 Replies View Related

Lookup To Return Multiple Values And List Sequentially

Mar 17, 2008

I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.

View 7 Replies View Related

Lookup Data From Validation Drop Down And Return Multiple Corresponding Values

Jul 17, 2009

I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.

For example:

A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006

How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.

View 9 Replies View Related

Lookup Function - Check Batches Of Data / Return Value Dependent Of Multiple Ifs

Apr 3, 2012

Use look up to check batches of data a return a value dependent of multiple ifs?

I have this formula from here

=LOOKUP(COUNTIF(C4:C7,""&0),{0,1,2,3},{19.9,29.9,39.9,49.9})

What I am trying to achieve is using the above can it check

C4:C7 Grouped and named "first"
C8:C10 Grouped and named "second"
C11:C13 Grouped and named "third"

If one from each group is selected it returns 1 value, if 2 of each are selected it returns a second value and if 3 of each are selected it returns a third value?

I thought by grouping them it may give me the desired results but all it does is change if I select more than one from any of the groups.

View 2 Replies View Related

Lookup Value In Array - Where Found Multiple Times Return Closest Prior Date

Mar 13, 2014

I have a list of ID Numbers and Dates. Let's call this "List 1".

I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.

I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.

What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:

List 1
List 2

ID Number
Date
Rating

111
17/04/13
5

[Code] ..........

View 9 Replies View Related

Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved