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.
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.
I am looking for Vlookup function, which returns multiple values without duplicates.
Please find the attachment : Vlookup Unique.xlsx‎
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 RelatedI 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?
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?
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)))
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.
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?
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.
I have a macro which returns the results for
=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))
As of today, the entry EMPLOYEE is sometimes written as it was EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?
Before it was:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"
I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+
"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"
as I would in Excel, but of course it doesn't work.
I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.
=VLOOKUP(A8,MA!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,PQ!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,ON!$A$8:$H$19,3,FALS E)+VLOOKUP(A8,TW!$A$8:$H$22,3,FALSE)
I wan to create a table using 2 column of data.Below are the data
Type Name
A Danon
B Chris
C Hilton
A Jin
A King
B Ling
C Seng
D Aaron
B Halim
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.
Worksheet1 has data.
Worksheet2 uses VLOOKUP back to worksheet1 for data to populate cells on worksheet2.
The VLOOKUP command below returns a value of 126 to cell C6. I want to use the number result of the VLOOKUP in C6 (126) as part of a calculation for another cell. In thiscase, cell D6 is keyed in. I want to get the result of =(N(D6)-N(C6)) and put it in cell F6. To further complicate things, if C6 is blank, I want F6 to be blank.
VLOOKUP formula that returns a value of 126 from worksheet1 and puts it in cell C6 on worksheet2. It also returns a blank if there is a blank on worksheet1.
=IF(ISNA(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0))+(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0)=""),"",VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,FALSE))
Add together D3+G3+J3+M3+Q3+U3 and put the result into W3? I think the reason it wont work is because there will not always be a value in D3/G3 and J3/M3
New KS4 grades Markbook.xlsx
I have a Sheet named Main, Juz and another called wordforword.
Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.
I do prefer formulas over macro if possible.
I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.
As a example i have attached the sheet.
I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.
Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.
i put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.
View 4 Replies View RelatedI have a problem with VLOOKUP function and I am looking for a workaround.
SheetA I have a column of business names and another column of the category.
SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...
I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used
=REPLACE('RAW DATA'!A3,1,2,"")
Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.
I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.
im trying to create a drop down menu that changes depending on the results of a vlookup. example: I have a vlookup function that populates a field with a product name based on the part number, however in some cases there are multiple products with the same part number. is there a way to create a drop down menu that contains all the product names shared by the same part number based on the part number that was entered?
View 1 Replies View RelatedI 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?
I have a workbook where one tab contains the data that I am given:
Project-A data1 data2 date1 date2 date3
Project-B data1 data2 date1 date2 date3
Project-C data1 data2 date1 date2 date3
Project-D data1 data2 date1 date2 date3
In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.
So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.
see the attached spreadsheet...I have a few notes of what I want to do. I think it is a vlookup, but i can't figure out how to do these.
View 2 Replies View RelatedI 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?
Is there to copy my vlookup results into another workbook?
I put together a marco that will select an range and paste it into a new document, however my vlookup results will not copy over.
Is there a way to extract my vlookup results and paste it into another workbook with the enclosed vba code?
Private Sub CommandButton1_Click()
'
' Macro2 Macro
' Macro recorded 4/24/2009
'
Columns("A:E").Select
Range("A2").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub
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 RelatedWorksheet 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
I have a worksheet and in one of my columns I can have multiple values, this could either be a valid numeric value, .e.g "Trace" or a value such as >1.5 or it could be blank.
Depending on the value I then want to return a different result in another cell.
In the example I have tried my data is in cell E8
I have tried the following formula and failed miserably, I'm not sure whether IF or IF OR can cope with this.
=IF(E8>=0,"1",IF(E8<0,"Invalid",IF(E8="Trace","2",IF(ISBLANK(E8),"4",IF(LEFT(E8,1=">"),"5")))))
If E8 contains a numeric value equal or greater than 0 then I want it to return a value of 1, this works
If E8 contains a value that is negative then I want my formula to then display "Invalid"
If E8 contains the word "Trace" to return a value of 2
If E8 is blank then to return a value of 4
If the first character in E8 is ">" then return a value of 5
