Vlookup: Returning Multiple Matches
Jun 22, 2009
I am trying to connect a special date and a price to a special name (please see the attached file).
In sheet 1 I have the following:
- In column A I have a set of different names.
- In column B I have a set of different dates.
- In column C I have a set of different prices on bonds that off course refers to the different names at their respective dates.
In rest of the sheets I want an overview of the different bonds, and what their prices are at the exact date. The problem is that if I use the vlookup it is only the first value that will be extracted. How can I get all of the prices corresponding to the different dates to the correct bond. I also want the possibility to update this every day.
View 3 Replies
ADVERTISEMENT
Mar 6, 2007
I am looking to perform a search that will return a match and place it somewhere else, and then search again until its finished the list?
View 9 Replies
View Related
Dec 12, 2011
I am trying to use the vlookup fxn to display employee names according their in-time (10:30 for this example). I have multiple employees in at 10:30 so I need to find a way using vlookup to accomplish this.
This is a sample of my Master worksheet showing the schedule for all employees (Cell used for this example is D16):
My vlookup formula for finding the first match for a 10:30 in-time:
Code:
=VLOOKUP(C5,Master!D4:$W$38,20,FALSE)
MasterABCDEFGHIJ9AndrewSmith10:30BQT10AnnaSmith11:30CBQT11:30C5:30C11:0011BrianSmith12Brittany
Smith11:30C11:30C11:0013CassieSmith11:30C5:0011:005:0014
DanielleSmithBARBAR5:30C15DemirSmith5:0016DerekSmith10:305:00OC5:0011:30CExcel 2010
The other worksheets in this workbook are various Floorplans depending on how many employees are scheduled that day with a column designated to the employee's name(B), and a column for their in-time (C). Here is a sample of the '8 Server Floorplan':
8 ServersABCDEFGHIJ52Derek10:3023243334632625353674Anna11:30C272892938511:30C4142615196#
VALUE!10:3043446352Excel 2010Worksheet FormulasCellFormulaB7=VLOOKUP(C7,Master!D4:W38,20,FALSE)A5=A4+1B5=VLOOKUP(C5,Master!D4:$W$38,20,FALSE)
A6=A5+1A7=A6+1A8=A7+1A9=A8+1B9=VLOOKUP(C9,Master!D4+P6:W38,20,FALSE)
Finding the first 10:30 match with vlookup is fine, my issue starts when trying to find the next 10:30 match, since vlookup only finds the first match (this is happening in B9).
Here is the sample of cells showing my formulas for finding the address of the first '10:30' match from my initial vlookup fxn (O5 and P5):
8 ServersOP3Cell Loc.Cell Loc. + 145D16D1767D1189Excel 2010Worksheet FormulasCellFormulaO5=ADDRESS(ROW(Master!$D$4:$D$38)+MATCH(C5,Master!$D$4:$D$38,0)-1,COLUMN(Master!$D$4:$D$38),4)P5=ADDRESS(ROW(Master!D4:D38)+1+MATCH(C5,Master!D4:D38,0)-1,COLUMN(Master!D4:D38),4)P7=ADDRESS(ROW(Master!D4:D38)+1+MATCH(C7,Master!D4:D38,0)-1,COLUMN(Master!D4:D38),4)
I thought by just finding the address of the first match (Master!D16), incrementing it by 1 (=D17) and plugging the cell containing this formula (P5) into my data range, that it would force the vlookup formula to adjust its data range from D4:W38 to D17:W38. This, in turn would then find the first match for 10:30 and so on for anyother identical in-times...
View 9 Replies
View Related
Jan 20, 2010
I've information in two sections of a worksheet, columns A and B (range named "temp1") and columns D and E (range named "temp2"). I should also say that the reason I have two sections is that each section will have about 150 rows, so instead of having a huge list of 300 rows, I've tried splitting them into two sections.
AB
1a
1b
3c
DE
4d
1e
6f
What I want to do it show all the results that match the value 1 using a vlookup formula. Also the values in column A and D may change, i.e if value 4 in D1 changed to 1, I would expect to see "d" included in the results.
This is the original formula I got somewhere else which sorts out my vlookup on multiple matches issue.
View 4 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
Dec 15, 2013
Trying to pull data from one workbook to another using Vlookup.
The worksheet has 7 columns (date, day of week, time or arrival, package, departure time, patron count and group name) I would like to pull all the data for a specific date in a seperate workbook.
I know I require a nested formula but cannot seem to get the variables correct:
View 2 Replies
View Related
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
Apr 18, 2014
I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.
Book2.xlsx
View 9 Replies
View Related
Jul 11, 2014
Basicly i have a list of information and i need to be able to enter a start data in one cell and a end date in a different cell and then return all the dates between them.
View 1 Replies
View Related
Jun 8, 2014
I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.
The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.
View 2 Replies
View Related
Apr 1, 2014
I have a reference vertical table which can be up to 30 long (I just put 5 here to keep it simple):
Sheet 1
A
B
1
[Code].....
And I want to return the data in the B column depending on whether the data matches either of the words in the corresponding A column.
E.g. Sheet 2
C
D
10
laki
The formula in this cell would return B1 because C10 = A1
11
katla
The formula in this cell would return B4 because C11 = A4
View 4 Replies
View Related
Aug 5, 2009
I have data exported as .txt from an external program in the following format:
Sample Name Marker Allele1 Allele 2 ....Allele n
AAA D8S1358 10 11 XX
AAA D16S539 16 19 XX
and so on.
Each "Sample Name" will have 16 different "Marker"s and upto 10 different "Allele" at each "Marker". There could be upwords of 200 Sample Names on each exported sheet. Is there a simple way to compare all of the data from each "Sample Name" to each other and possibly to another sample set that will always be static but of the sample general makup as this?
View 9 Replies
View Related
Mar 21, 2014
I have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
View 4 Replies
View Related
Mar 21, 2014
I am trying to find a formula that populates a room number based on the number of hours used. I have a chart that is by building but multiple rooms within each.
Where I am having trouble is when there are more than one room with the same number of hours used then sometimes the formula populates the wrong room number.
Attached is my example spreadsheet of my chart. Book1.xlsx
I have shown what I need, what I have tried and a blank space to show where I need it to go.
View 3 Replies
View Related
Aug 25, 2012
if a value appears in a column 4 times or more how can you do a vlookup which will show the 1st 2nd 3rd and 4th values.
when i currently write the vlookup formula it shows the 4th time the value appears
=VLOOKUP(B5,B14:D1854,3,0)
View 9 Replies
View Related
Jul 24, 2006
I am getting #N/A errors even when I have an apparently exact match in my
table array to the lookup value. I know that excel requires the formats to
be exactly the same and I can force the match if I copy the lookup value from
my table array and paste it into the worksheet. Is there an easy way to
"fix" my table array. The table array is a reference worksheet I have
created which has worked in the past.
View 13 Replies
View Related
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?
View 9 Replies
View Related
Nov 13, 2009
I want to vlookup from sheet2 cell E2 to find all matches in sheet1 in coulmn G and add totals together in coulmn E. The match I am looking for is used in several rows of coulmn G on sheet1. I only want cell E2 on sheet2 to give me total amount from data in coulmn E of sheet1 when a match is found in coulmn G of sheet1.
View 9 Replies
View Related
Jan 25, 2010
I need to add a space in front of a string of numbers/letters, but it still doesn't seem to match what's in the lookup range. Granted, i get the lookup range from HQ, so there may be a formatting issue.
View 2 Replies
View Related
Jul 2, 2014
I have several Campaign Names (about a thousand) and about 25000 categories (with ID's) to choose from and would like to assign the Category ID's to the Campaign Names without doing manual searches. My problem is that the names are not similar enough for vlookup and I didn't get any usable results with the fuzzy lookup add in either.
I will attach an example of both my Campaign and my Category file for reference. Example Complicated Vlookup.xlsx
View 3 Replies
View Related
Nov 23, 2008
i have a drop down list in cell b16 which then corresponds with what to bring up in the next drop down list in cell d29 this works fine.
View 2 Replies
View Related
Jan 22, 2009
I have just decided to dable in the 'VLOOKUP' function. I have looked at a few examples on the net and I have tried to replicate them with no luck (I have modified titles and values). I have attached my example to this post.
View 2 Replies
View Related
Oct 11, 2009
=IF(ISERROR(VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,11,FALSE)),"",VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,10,FALSE))*D14
As long as the lookup value is true the formula works but if the lookup value is false then I get #value! when it tries to multiply by D14. How do i get the formula to return a blank cell or 0 value if the lookup value is false?
View 5 Replies
View Related
May 20, 2009
I'm hoping someone can help. I've written a =VLOOKUP formula that works fine. What I'd like to do is if the value can't be found within the lookup then the formula returns a 0 rather than the standard #N/A. Can these be joined up in 1 formula?
View 6 Replies
View Related
Nov 13, 2006
I have a VLOOKUP formula that is working just fine in Column O.. When I drag it over to Column P (changing nothing in the formula only changing the month) why would it now return #N/A. There is nothing wrong with the source that I can see..
View 9 Replies
View Related
Oct 14, 2006
the following vlookup is not returning a value in the specified cell (C39). From the threads I have searched through it looks OK but not working
Sheets("Day 1").Range("C39") = Application.WorksheetFunction.VLookup(Sheets("Day 1").Range("M2"), Sheets("PCO").Range("B1:C12"), 2, False)
Im sure from past experience I have missed a comma or something but buggered if I can find it!
View 5 Replies
View Related
May 26, 2007
I have a problem with a table, works fine for the first five rows then doesn't work afterwards. Can anybody see what I'm doing wrong?
(File attached)
View 9 Replies
View Related
Nov 28, 2012
I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.
My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.
View 4 Replies
View Related
Jul 3, 2013
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
View 9 Replies
View Related
Jun 23, 2014
I am using vlookup to return data that i have hyperlinked in another sheet. But when it returns the data, the hyperlink has gone.
VLOOKUP HYPERLINK RETURN PROBLEM.xlsx
View 4 Replies
View Related
