Vlookup Data And Return Results On Multiple Entries

Feb 16, 2009

So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.

I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.



ADVERTISEMENT

Concatenate Multiple Vlookup Results With Dash Exclude Null Values Or 0 Return?

Jun 10, 2014

The 3 vlookup will be in a single cell for concatenation like :

=vlookup1&"-"&vlookup2&"-"&vlookup3

Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.

I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.

View 6 Replies View Related

How To Get VLOOKUP To Return Multiple Instances Of Data

Jun 23, 2009

Sheet 1 has one column

Customer Number (unique)
e.g.

111
222
333
444

Sheet 2 has two columns

Customer Number
Email Address

e.g.

111-----billgates@msn.com
111-----billgates@microsoft.com
111-----billgates@hotmail.com
222-----davidbeckham@mufc.co.uk
222-----davidbeckham@fa.co.uk
333-----me@test.com
444-----you@test.com

Now, when I do a vlookup on sheet one to obtain all email addresses for customer 111, it only returns the first email address - e.g. billgates@msn.com

Ideally, I would like;

Is there a function that can achieve this?

If not, can the multiple occurrences be returned in one cell, separated by;?

View 9 Replies View Related

VLookup Return Multiple Hits From Same Column Of Data

Jul 14, 2009

I am running a Vlookup on a large set of ID#'s that exist accross multiple servers.

The data is structured in (2) columns how you see below , and you will notice that the same ID# can show up on multiple servers.

However, the VLOOKUP is only returning the first match it finds.

Assuming that I want to keep the data structured as is, how do I make the vlookup return all matches?

ID# LOCATION

23 SERVER1
34 SERVER4
23 SERVER2
13 SERVER1
17 SERVER3
34 SERVER1

View 10 Replies View Related

Vlookup To Return Results Based On Different Sexes And Ages

Nov 4, 2008

I want vlookup to return results based on the *** and age of the person from tables in a separate sheet. For eg. if the person is "male" or "female" and if the person is "<=15", "16", or "17". I have been practicising with a formula that looks like this:

View 6 Replies View Related

VLookup For Multiple Entries

Feb 10, 2014

I have an excel file containing more than 70,000 lines (items and their corresponding orders) and in second file i have all the items listed. I want to find how using excel functions like vlookup i can return all the orders (from first file) against the items (in second file).

Attached a simple example of my problem. Please note that both tables are in different sheets of an file.

ITEMS
ORDERS

A
2

B
3

[Code] .......

View 7 Replies View Related

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

View 3 Replies View Related

Doing A VLOOKUP And Returning Multiple Entries

Jan 30, 2009

I've tried to amend the formula's posted above but to no avail so am hoping someone can have a look at the attached.

I would like to be able to use Vlookup or similar to complete a table (starting in cell A19) based on the surname added in B16.

View 2 Replies View Related

VLOOKUP With Multiple Entries For The Reference?

Feb 4, 2010

I love using vlookup, but what do I do if the value I'm looking up is listed more than once in my array? The default is that it will use the first value found.

I have a spreadsheet with 1000's of commodities and for each commodity the volume is broken out by month. For example, here's the sheet where I'm pulling the data from:

ITEMNOPERIODESTCSTREQQTYACTQTY

RC0202010010.109949140005867316
RC0202010020.109939312004668300
RC0202010030.109958968000
RC0202010040.109941769000
RC0202010050.109919656000
RC0202010060.109941769000
RC0202010070.109949140000
RC0202010080.109951597000
RC0202010090.109951597000
RC0202010100.109951597000
RC0202010110.109973710000
RC0202010120.109956511000

So my vlookup for the RC020 for the december Actual Quantity will default to the 5867316. For for January (period 2), if I do the vlookup it will still take the 5867316 again since its first in line.

So my question is, how do I alter my formula in January so it will vlookup the 2nd RC020 and give me the "4668300"?

View 9 Replies View Related

VLookup With Multiple Results

May 8, 2012

I have a large spreadsheet of data, with lab results by date. Sometimes, there are multiple data points for a single date.

I need to produce monthly reports that list all the results for specific tests in a given month.

So, for example:

Date SG Property Tank
1/5/12 1.015 567324 2044
3/15/12 1.002 568210 2103
3/18/12 1.025 568056 2044
3/18/12 1.036 565200 2102
4/1/12 1.019 566713 2103

I would like to make a report for March that looks like this:
Date SG Tank
3/15/12 1.002 2103
3/18/12 1.025 2044
3/18/12 1.036 2102

And the next month, make a report for April, then May... etc.

I tried to think of a way to do this using vlookup or index/match, but couldn't figure it out.I also tried using filters and then automating some kind of copy/paste, but there is an extra line between the heading and the data, so that the date column is filtered as text instead of date. The source data is not my spreadsheet, so that would be difficult to change.

View 9 Replies View Related

VLookup With Multiple Results?

Dec 17, 2012

I would like to do a lookup function, in which multiple results are returned.

i.e.

VLookup Result 1
Result 2
Result 3
Etc.

I know how to do it if there is only one answer but say I have a unique identifiers in which I want all the results in column B:B displayed one after another, say locations have numerous products sold/manufactured at it.

View 1 Replies View Related

Vlookup With Conditions To Find Multiple Entries

Feb 2, 2009

I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.

A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15

I have another table (table2) in another sheet in the same workbook have a material and date.

A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008

My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.

I have tried using vlookup but it only works for the first match and doesn't check for other values

below is the function that i tried

=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")

View 14 Replies View Related

VLOOKUP - Bringing Back Sum Of Multiple Entries

Jun 12, 2014

I have a VLOOKUP that needs to bring back the sum of all entries that meet the criteria. At the moment it is only bringing back the first entry.

View 3 Replies View Related

Vlookup For Multiple Results Without Duplicates

Sep 20, 2012

I am looking for Vlookup function, which returns multiple values without duplicates.

Please find the attachment : Vlookup Unique.xlsx‎

View 3 Replies View Related

Displaying Multiple Results With VLOOKUP

Dec 8, 2009

Hi, I'm sure this will be an easy one for most of you. I need to extract data from an array for each time I have recorded a specific vehicle registration. I have used VLOOKUP but this only gives me one result. I need excel to display every enrty for the registration I am looking for. I have attached a sample file which should make more sence.

View 7 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 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

VLOOKUP Multiple Results In A Drop Down List

Feb 12, 2009

I am using the VLOOKUP function to pull multiple columns of information from another sheet in the same workbook based on a name. The sheet it is pulling from is a query. I am looking up product names, and in the query there may be multiple results that the function can find.

VLOOKUP only returns the first result that is found. What I am trying to do is get it to dump all the results into a drop down list in one cell. If I attach a drop down to one of the columns of information I am retrieving, then I can select which of the entrys to display, right? I think this should work I just don't know how to go about setting it up to do it.

How would I set up a VLOOKUP formula that attaches a drop down list containing all the results that the function finds?

View 7 Replies View Related

Multiple Vlookup Results As A Text String

Apr 24, 2009

I have searched other postings but can’t seem to find the answer.

I was wondering if anyone had a solution on how to string multiple Vlookup results as a text string so that if I Vlookup “Pear” it will return “Red, Blue, Green” in adjacent cell
i.e

A B
1 Fruit Colour
2 Pear Red
3 Orange Purple
4 Apple Green
5 Banana Yellow
6 Pear Blue
7 Pear Green
8 Banana Black
9 Orange Green
10 Apple Black

Is Vlookup the right function to go with here?

View 9 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

Can Index Or Match Return Multiple Results

Nov 27, 2006

I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!

I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status

plus a whole bunch of other columns of information.

On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.

I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.

Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.

View 12 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 Results Based On Criteria

Dec 1, 2006

Please see the attached sheets.

Here's the issue:

On sheet "master" I have a list of goals in column A.

Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.

I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.

I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.

Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.

how this done for AL, I can go ahead and create the other sheets.

View 9 Replies View Related

VBA Array VLookup And Multiple Results Output In One Cell

Oct 21, 2012

What I need is a script or formula with returns multiple results and puts them into one cell.

The Excel is used as a Project Managment Work Sheet for Resources, working on different Projects and their dedicated hours

The raw data table looks like this:
Column A: Project Name (=AllProjectsLists)
Column E: Resource Name (=MasterDataResources)
Column P: values from 0 to 8 (hours)

First I need to check per row the Resource Name. If the Resource Name is a match, than I need to check if in column P the value is bigger than 0. The result goes into a different sheet per row, one result per Resource, but the multiple results should be shown in one cell.

So the result should be something like:

Resource name "Thomas" .... Projects working on: "Project 1, Project 5, Project 13, ..." (in one cell)
Resource name "Mary" .... Projects working on: "Project 3, Project 9, Project 13, ..." (in one cell)

I tried with this one, but it only returns one vale per cell:

=SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1), ROW(1:1))
=INDEX(AllProjectsLists; SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1); ROW(1:1)))

View 5 Replies View Related

Dropdown VLookup Menu Returns Multiple Results?

Oct 11, 2011

I'm attempting something new, a dropdown menu using vlookup to find data . If i use the simple example below, using data from B-D on 1 worksheet, where i want to find people by county:

A B C D
Search by County Name County Town
John Sussex Brighton
Eric Yorkshire York
Jim Cheshire Manchester
Liz Sussex Hove
Sarah Avon Bristol

I've created a dropdown menu on a second worksheet,A2, by county search, to return a persons name,county and town. The problem is if i select Sussex i will get 2 names who live in different towns within Sussex. I've created a simple vlookup which works fine, but because of Sussex having 2 results, i created a second vlookup for the second result to appear in the cell underneath.

The problem is that for the results that only have 1 result, the second vlookup returns a the same result as the first result, which i dont want.

Is there a way where multiple results will be shown if they exist, but for one result to appear if only one result exists?

View 9 Replies View Related

Single Formula/Function Return Results To Multiple Cells

Aug 26, 2006

I've got an If statement in Excel which features several VLookups - I need the IF statement to return differing results to 2 different cells, is this possible?

In plain English, I'm looking for something like

If(Vlookup(A2, B2:D5, 4, False), C6="y" And D7="ok", "False")

Obviously this is a very crude example

View 3 Replies View Related

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

View 4 Replies View Related

VLookup To Output Text Data Results?

Dec 24, 2013

I have a column with lots of tube station names. I have a look up table with the tube station name, and the possible lines that pass by that tube station.

One tube station may be covered by more than one tube line.

I am looking for a formula that will output every tube line that caters that one tube station.

first question, what is the best way to organise the data in my look up table.
Second, is a vlookup the best formula to achieve the output I want? i.e. Reflect how many tube lines cover each station

see table below: If the station name has only one tube line, Vlookup works fine. When the station, such as Canno Street, has two tube lines, Vlookup only picks up one tube line. If I duplicate Cannot street under the station name, the Vlookup picks only one tube line.

LOOK UP TABLE A1 - station name
B1 Tube line name
Cannon Street
District

[Code].....

Is there a formula that will output the data of the second tube line name in column D1?

View 3 Replies View Related

Multiple Return Vlookup

Jan 9, 2007

Is there a way to get a vlookup for return multiple values if there are reoccuring numbers? for example

A 1
B 2
C 3
D 4
A 5

If those were my two columns, could I get the value A to return both 1 and 5?

View 9 Replies View Related

Vlookup To Return One Value From Multiple Columns?

Sep 21, 2013

I have two separate worksheets, and I am trying to create a Vlookup or Index and Match formula. Here is the example:

Sheet 1
Cell A1= Employee ID: 123-D.

Sheet2
Vlookup A1 from Sheet 1, and match the first five characters to Column A, Column I and Column P. If a match, return name (e.g. John Doe) in Sheet 1, cell B1.

View 9 Replies View Related







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