VLookup With Multiple Matches?
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
ADVERTISEMENT
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
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
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
Dec 2, 2013
I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.
The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).
And I want it to extract "Bills" value from "rule" sheet.
There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.
How do i solve this problem with index-match function?
View 3 Replies
View Related
Jan 14, 2009
I need to create a formula that will give me True or False if text in a cell matches any cells in a range.
For example:
Column A and B have text:
Account
Date
Name
Species
Column C has other and sometimes matching text:
Name
Deal
What formula would I use to find out if all cells in Columns A and B are represented in Column C?
View 3 Replies
View Related
Dec 10, 2006
I am trying to match data on two different reports. Both reports have month, location and supplier number. I would like to match a quantity on one report to information to another. Here is an example: (A-D are columns in excel)
Report 1:
A B C D
Month Location Supplier # Quantity
Report 2:
A B C D
Month Location Supplier # Quality
I would like to match the quality on the second report to the data in report one. I would have several months of data and want to match on month, location and supplier number. But there could be occurences where a match is unable to be made - for example having report with quantity but no corresponding quality on the other report for the month.
View 9 Replies
View Related
Mar 16, 2007
I am trying to set up a worksheet whereby two critera when matched from a drop down list will populate cell 'x' with a coressponding answer. if you have Destinations then a From and To column with various locations listed below, then in a thrid column you have an output cell which is kilometres. What formula can i use to match the various location 'to' and 'from''s with the correct kilometre match?
View 8 Replies
View Related
Dec 15, 2005
I have some values representing daily values belonging to a particular Week number. The week numbers appear from 1 to 6 times in the top row. I would like to get data pertaining to a particular week number (as entered in B10) from the array in the area shown in Light Yellow and from there, I would do some minor calculations to arrive at the required data..
In the sample, the week number 3 appears 3 times and is shown here. The first value I am able to get using HLOOKUP. Getting subsequent occurrences are the issue I have. I thought I will try and use OFFSET function based on the cell reference of the first occurrence of the week number to obtain the rest. I have not had any luck in that.
Basically, my issue is how to use HLOOKUP to obtain if the Lookup value is repeated in the range. Or should this be tackled using some other route?
The week numbers appear in a sequence and are not placed at random.
View 9 Replies
View Related
Jun 8, 2014
i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?
e.g. worksheet 1 has all the data
A B C
email ID date
worksheet 2 has a list of specific email addresses i'm looking for info on
A B
email date
My formula in column B of worksheet 2 is along the lines of this:
index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))
basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.
The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.
View 6 Replies
View Related
Jun 17, 2014
I am trying to populate a worksheet that takes information from a table on another worksheet. I have to match three columns and show multiple entries for each correct match. Attached is a sample worksheet.
Invoice.xlsx
The information should be on the "Invoice" sheet. The cells in green are what will be given. The cells in yellow are what should be grabbed from table 1 on "June Sands Tracking Sheet"
The delivery date, Customer and truck number are the given fields.
from there I want it to search the table and populate "Truck BOL #","Sand Yard","Time In Staging", "Time Out of Location", and "Sand Type"
I tried this formula in the BOL # column but it wouldn't work
=IF(OR(C10={"",""}),"",IF(COUNTIFS(Table1[Delivery Date],$B$7,Table1[Truck '#],$E$7)=0,"No Entry",IFERROR(INDEX(Table1[BOL'#],SMALL(IF(Table1[Delivery Date]=$B$7,IF(Table1[Truck '#]=$E$7,ROW(Table1[BOL'#])-MIN(ROW(Table1[BOL'#]))+1)),ROWS(C$11:C19))),"")))
View 3 Replies
View Related
Jan 16, 2012
I am trying to get a formula to work that looks up a vendor, and then checks the quantity of their order (less than 99999999, less 500, less than 200, and less than 50). I get either a #NAME or #N/A when I try to use what I have written. I tried to include an =index(array,AND(Match(row 1,exact match),Match(row 1 less than quantity),Match(row 1,greater than quantity)),Match(rate,exact match)) returning the variable rate. I have had no luck using vlookup and pivot tables.
View 6 Replies
View Related
Mar 25, 2012
how do i lookup values when there are multiple matches and i want to return values horizontally
eg
column A Column B
A 1
B 2
C 3
A 4
B 5
C 6
D 7
D 8
D 9
D 10
and the output should be like:
column A Column B Column C Column D Column E
A 1 4
B 2 5
C 3 6
D 7 8 9 10
View 7 Replies
View Related
Sep 27, 2012
So here is how I have a template laid out:
A
B
C
D
E
F
ROW 1
Period 10
Period 11
[Code] ........
Cell E4: =SUMIF('P10-2011'!$A$4:$A$272,'Budget Upload'!$A19,'P10-2011'!$H$4:$H$272)
I have a SumIF statement in cell E4 but I would much prefer some sort of index or something where I can drag the formula down because the cells in column D will change (and require me to pick a different SUM range for my SUMIF function). Now my issue is I have multiple matches so when I use a typical index it returns the FIRST match.
Here is the formula I used in cell E11 to try and replicate the result in E5:
=INDEX('P10-2011'!$A$2:$Y$272,MATCH('Budget Upload'!$A4,'P10-2011'!$A$2:$A$272,0),MATCH('Budget Upload'!$D4,'P10-2011'!$A$2:$Y$2,0))
My index function works as it is designed but only returns the FIRST match. I should have multiple matches.
Is there a way to use an index function to return multiple results?
To clarify,the index function I put in cell F4 returns only ONE column (which is correct and will always be correct), which is related to the cell D4 but I have multiple rows (related to "7002" row match portion of the index function).
View 3 Replies
View Related
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
Apr 29, 2009
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies
View Related
Jan 29, 2010
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
View 4 Replies
View Related
Jun 8, 2014
what i'm trying to do with an index match formula. My index match formula goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?
e.g. worksheet 1 has all the data
A B C
email ID date
worksheet 2 has a list of specific email addresses i'm looking for info on
A B
email date
My formula in column B of worksheet 2 is along the lines of this:
index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))
basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.
The thing is we could have the same email address listed a number of times, so i'm looking for the latest date to be returned, not the first one it finds.
View 1 Replies
View Related
Aug 21, 2009
I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.
=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))
View 5 Replies
View Related
Mar 31, 2008
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.
The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.
I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:
(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)
I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
View 10 Replies
View Related
May 8, 2013
I want to use Hlookup to get a non-blank values from a list that has multiple matches.
my data
a b a c d
1 2 3 4
if i match 'a' my result should be 2, by skipping the first blank.
View 8 Replies
View Related
Dec 2, 2007
I am trying to compare mutiple columns of data, match them and copy select data. Find matching cells in 2 different columns and copy select info into that row. See file attached
View 7 Replies
View Related
Jun 19, 2014
Check the attachment, i could not make out this using vlookup, how to overcome this problem.
test.xlsx
View 2 Replies
View Related
Sep 15, 2014
I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:
Car
Region
Own
Use
Color
Honda
North
Yes
I use it to go for work
Green
[Code] .....
I want to be able to do following (2 separate tasks):
Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):
Use
I use it to go for work
Family trips
Weekend fun
2nd car
Work
Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):
Car
Region
Color
Honda
North
Green
White
I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.
View 2 Replies
View Related
Mar 26, 2009
Column A would be Recipe Numbers, Column B the Planned Total.
I want to search column A for all matching recipe numbers.
Then, any recipes that match, look in column B for the planned totals, adding all incidents where they match.
Then the most difficult part, which just occured to me now.
I only need the Total Planned Total and Recipe name reported once.
Recipe#..........Planned Total
450................100
600................75
620................125
450................50
620................180
450................100
600................200
450................110
I will not be able to sort these lists.
If-Match-Sum? to display:
Recipe#............Total Planned Total
450....................360
600....................275
620....................305
I've researched for the first part... and it seems like Match will only find the first match and not look further? Am I wrong on this?
How to display the results hadn't even occured to me yet, since I was still trying to figure out how to GET the result.
View 6 Replies
View Related
