Vlookup Data And Return Results On Multiple Entries
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.
View Complete Thread with Replies
Related Forum Messages:
How To Get VLOOKUP To Return Multiple Instances Of Data
Sheet 1 has one column
Customer Number (unique)
Sheet 2 has two columns
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. email@example.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;?
VLookup Return Multiple Hits From Same Column Of Data
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?
Displaying Multiple Results With VLOOKUP
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.
Vlookup With Conditions To Find Multiple Entries
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
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
VLOOKUP Multiple Results In A Drop Down List
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?
Multiple Vlookup Results As A Text String
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
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?
Return Multiple Results Based On Criteria
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.
Can Index Or Match Return Multiple Results
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
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.
Multiple Return Vlookup
Is there a way to get a vlookup for return multiple values if there are reoccuring numbers? for example
If those were my two columns, could I get the value A to return both 1 and 5?
Using VLookup To Return Multiple Instances
I am looking for a formula that will return the cell numbers of multiple instances of the item found using VLOOKUP. For example if I am looking for
"A123", this may occur in more than one cell in the search range. I want to be able to return all the cell reference numbers of that instance.
VLOOKUP To Return Multiple Values
I'm having trouble getting my payroll data to combine. I've been using VLOOKUP to look up employees by their Personel Number (Column A) and return their name (column B), Cost Center (Column C), the Fund that they are paid from (Column D) and the Amount they are paid (Column E). The problem is that some employees are being paid out of 2 or more funds and VLOOKUP is only returning the fund that appears first, leaving out the other portion of their pay and thus making my grand total quite far off. How can I get Excel to return all the entries for a particular Personel Number? There are more than 16000 employees, so this is no small spreadsheet.
Vlookup With Multiple Text Value Return
I'm trying to create a formula that returns with all the text values for all the conditions. So similar to a sumproduct where the formula returns a summed value of ALL the conditions i need the same but for text.
formula would return with (no, no, yes).
Vlookup From Multiple Columns With Only One Return
I have a spreadsheet with twenty columns. Column A has an item number (say "Clutch"), and the remainder of the columns have values. However, there only be one column in the range B:T which will have a value on the same row as "Clutch" (say "Black" in column "N").
How I can I return "Black" using a vlookup or should I be using something else?
Use A VLookup To Return Multiple Values
I have a workbook with 2 sheets. The sheet called 'data' contains all purchasers names (which do get repeated), the item they bought and 3 item costs, all in range A1:E15. The 'lookup' sheet allows the user to enter a purchasers name in cell B1, then I want all the items that purchaser bought listed with the corresponding item costs. I've attached a sample spreadsheet.
Vlookup Return Multiple Values In One Cell
I tried searching for a solution to my formula problem in this forum regarding vlookup and saw similar formula e.i returning multiple values but it doesn't cater with what I need.
Vlook is returning only the first value but what I need is to return all the multiple return values in 1 cell. I've attached a sample worksheet for reference.
VLOOKUP To Return Multiple Cells Of Same Lookup Value
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.
(multiple) VLOOKUP Function To Return Blank Field On False
Where in this function would I put " " to return a blank field if false is returned?
Lookup Single Value In One Sheet, Return Multiple Results From The Other Sheet
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
Vlookup 2nd Cell To Return The Data
I am struggling with the Vlookup function on Excell 2003. I am trying to lookup data with refernce to two cells to return the data. for example cell A1 = 1 AND cell B1 = 2 then return column 3 data. However I need this in a lookup or something very similar.
VLookup To Pull A Value Out Of A Range And Return Intended Data
I am trying to use a VLookup to pull a value out of a range and return my intended data. See attached spreadsheet. The data I'm pulling from is in the top chart. The bottom chart, in the factor column, is where I want the data to pull to. Ex) If the "Months of Development" column in the bottom chart =13, I want it to pull the factor value from the top chart where the month is 13. The problem is that the top chart has a range of values in the "Month Range" column of 13 - 24. I don't know how to get the VLookup to recognize this and pull the same factor into the chart below whether the value is 13 or 24. Maybe VLookup isn't the best option here.
List Unique Entries And Total Cells Based On The Results
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
VLOOKUP For Multiple Data On 1 Sheet?
Can you use VLOOKUP or a similar formula to lookup and add (or even better average) multiple data from one sheet?. For example the source sheet holds certain codes for each of our agents and I need the total of one particular code for each agent and then eventually work out the average for the day. But as the source sheet can hold 30+ agents it would be a long and tedious job to do seperate Vlookup's (especially as we are backdating the last year!).
I have attached a snapshot of the source data to try to explain what I need better.
From that source data I need to get the total/average of all the 101 codes.
VLOOKUP - Multiple Data Range(s)
I extract data from a system and originally it extracts it in one sheet.
I would then use another list on another sheet and see if there is a match from the first sheet.
What problem i am having now is that the extraction from the system comes through in two sheets(because its too large) so my original VLOOKUP statement which was: =IF(ISNA(VLOOKUP(A2,cobject1,1,FALSE)),"No","Yes") only works for the one "sheet".
But now I have two sheets which hold the table "cobject" so i have named the first one "cobject1" and second; "cobject2". I figured out a way to do it but i would like the formula that i would need for me to use, for me to check the above match but from the two different "tables".
Using VLOOKUP To Retrieve Multiple Data For The Lookup Value
I have to write some VLOOKUP formulas to pull several items from a report, the problem is there could be multiple items for each lookup value as the example below:
Company Data ABC Monday ABC Friday ABC Thursday DEF Friday GHI Monday
I need to reference every piece of data for every company but using the VLOOKUP formula, I am obviously only able to get the first instance of the lookup value (company ABC)
How to I write the formula to lookup the other data? The practical appliation I am using this for has anywhere from 300 to 400 lookup values.
Code To Return Data From Multiple Work Sheets
I have a folder on a public drive. (\CFDIVIX1PublicPurchasing2009)
This folder contains multiple sub-folders (the sub folders are vendors which I purchase merchandise from) each sub-folder has 1 .xls file in it. Each file has 1 sheet containing all the information I need to retrieve.
Each one of these files has the same header information in cells A1 through I2 so I manually place that when I create this new workbook prior to inserting the macro. Each work sheet has a different # of rows depending on the number of items I buy from that particular vendor but I feel safe saying it never exceeds 250 rows per worksheet.
What I need
For the macro to pull up the first work sheet and pull out the data from cells A3 through I250 and place that data into my new work sheet in the same format. Then I want the code to move on to the next worksheet and pull out the A3 through I250 and place that data in the new worksheet directly below the last row of data that was pulled in. (Also it would be great if the code ignored blank values. Like if the first work sheet it pulled data from only had data in the first 10 rows I don’t want 240 rows of blank rows before the next set of data)
I hope that running this macro will accomplish transferring all of my data from these multiple sheets into a single work sheet containing all of the pricing for all of my vendors. Once I have this I can place a second sheet on the workbook and use a formula to pull out pricing information that I need on a per basis without having to search through 50 vendors to find who it comes from. I don’t control the vendor work sheets that I want to extract data from so I need to use the macro to run it weekly incase my purchasing dept makes changes.
Return Multiple Instances Of Data Related To Number
The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.
The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied
Private Sub FindAll()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("ChargeNo.")
strFind = Me.CbAdjFind.Value 'find Charge No
Set c = .Find(strFind, LookIn:=xlValues)..............
I wan to create a table using 2 column of data.Below are the data
I wan to convert these data to a table which have four dimension. A, B, C, D. With Vlookup function, i can do it. But, i find difficulty when the result is overlap, when in the type A, got Danon, Jin, and King. My result only can lookup is King. Other cannot display.
Vlookup - Pick Last Of Duplicate Entries
Writing this workbook in 2007, but it will be used in 2003. Have searched but can't find answer to this. I have a 5-column (A to E) table array and I'm using VLOOKUP in several other cells to return values in col2, 3, 4 & 5 with my lookup value being col 1. However, col 1 will contain numbers which occur more than once. How can I get VLOOKUP (or something else?) to pick the last occurence (lowest row) and use that one? Also, the array will be sorted - smallest to largest - as the rows go down - how will the SORT work on duplicate entries? I'd like, if possible, for the entry made most recently to be the lowest of the duplicate entries so that VLOOKUP (or whatever) will pick that one to use. Will any kind of SORT do that?
Lookup Data From Validation Drop Down And Return Multiple Corresponding Values
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.
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.
Add VLOOKUP Results Together
I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.
Return Top 10 Entries For Each Category
I have a product database that contains in column A the product name (eg "Granny Smith"), in column B the category (eg "Apple") and in column C the number of sales (eg 10,123).
I was looking to use code to list the top 10 products for each category, so to show the top 10 names I would enter in something like...
Return Last & 2nd Last Entries
I am trying to construct formulas that dynamically extract correct values. I need to construct two formula one in Column I and Column J. Column L and M have solutions that I should have. The formulas need to look for data in columns and extract them. For example, if an entry in E3 then for This week it will extract it and for last week it would extract entry in D3.
Formula To Return Unique Entries Only
I am seeking to pull names from a list on one worksheet in to particular cells on another worksheet using each name only once. I need a function or series of them that can accomplish this. I have been trying to figure this out for a couple of months with no luck as I always get into some form of circular reference....
Check A Value And Return Results
I have a table that has 6 columns and 5 rows. I need to check a name (division) against the column heading, then and return a classification from the first column based on the score.
Below is an example of what I am using (I'm not sure how to post a screen shot):
Total Score 155.6
Formula Return The Same Results
The formula in Col G uses the value in col's B:E. I would like a formula in Col G that will use the 4-digit value in Col F ( same values as Col B:E) and return the same results.
Search Tabs And Return Results
I would like to summarize all "yes" respose contained in 15 tabs. See the attached.
I would like results to be returned to the "summary tab" when user answer "yes" in the signficant column in the "process activity 1 through 15" tabs. If the answer is yes, I would like to pull the process name, aspect and impact detail.
Return Lookup Results Of All Occurences
I am using vlookup to return data from a range that could have the same name in column A.
There are 8 columns
Column 1 is Name
Column 2 is Sun
Column 3 is Mon
and so on.
The problem I have is that a name can appear in two places in column1 with relevant information in different columns:
Fred appears twice, on Wed he is on a course the rest of the week he is on leave. I need a C to be returned for Wed & A/l for the rest of the week.
Vlookup, Background Colour Change Of 10 Results
I have a sheet that uses V lookups to return names, there are 10 diff names throughout the sheet it could return
I want to change the background colour of the cell depending on what the name is. the v lookup is run in collums A - L and 100 rows down
How can I code it so each name has its own background colour so the Cell changes according what the V look up returns?