VLOOKUP / INDEX And MATCH To Return All Values That Match
Feb 4, 2014
I have two sheets of data , sheet A and Sheet B.
Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.
I have filled out the first two rows of results that should be returned as an example.
One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.
View 3 Replies
ADVERTISEMENT
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
Jan 21, 2014
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
View 3 Replies
View Related
Mar 1, 2014
making a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results
View 6 Replies
View Related
Aug 26, 2009
I have a list of data that I want to average the number in column B when there is a match in column A
COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500
What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.
View 4 Replies
View Related
Jan 19, 2014
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
View 7 Replies
View Related
Aug 28, 2012
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
View 5 Replies
View Related
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
Oct 22, 2009
I have a worksheet that has numerical data in A1:A22. In E1:E61 I have set values in numerical order and in F1:F61 I have a letter or letters that correspond to E1:61.
I am trying to write a formula in B1 that will look at the value in A1 and then match it up in E1:E61 and then return the corresponding letter in F1:F61.
e.g: in A1 i have an INDEX and MATCH formula
View 2 Replies
View Related
Apr 3, 2008
I have two worksheets that I am hoping to use to generate one report in excel. Sheet1 contains the following information for my entire work center:
Benefitor Account Dollar Value Document #
Sheet2 contains a list of Benefitors that are relevant to only me.
I want to pull the information from Sheet1 where the benefitors on Sheet1 match the benefitors listed on Sheet2 into Sheet3.
View 11 Replies
View Related
Dec 4, 2008
I have a tab that has Employee Name in one column and the benefit they chose in another column. The employee name is repeated a number of times since they have chosen more than one benefit.
I am trying to create a new tab where the employee name in is the first column (only once) and each benefit is listed in separate columns along the top. I want to put an "x" in the column for the benefit the employee has chosen.
Is there a way to say "look for this employee, see if this benefit is what they chose, and if it is, put an "x", if not, leave blank"?
View 9 Replies
View Related
Jun 5, 2009
I am trying to get the info shown on table 1 into table 2. I suspect index and match is the answer, but I am not sure.
Table 1 Employee # Deduction Amount 101 AD&D 7.00 101 LIFE 4.00 101 LTD 12.00 101 STD 6.00 101 HEALTH 300.00 101 DENTAL 25.00 105 AD&D 8.00 105 LIFE 6.00 105 LTD 6.00 105 STD 300.00 105 HEALTH 25.00 105 DENTAL 8.00 112 AD&D 7.00 112 LIFE 5.00 112 LTD 13.00 112 STD 5.00 112 HEALTH 450.00 112 DENTAL 37.00 Table 2 AD&D LIFE LTD STD HEALTH DENTAL 101 105 112
View 9 Replies
View Related
Jun 25, 2009
I have two active worksheets. The first one is called 'Chart' and the second is 'Values'. I have three columns of data. I would like to look in the first two, and return a value in the third. =INDEX(Values!$C$1:$C$299,MATCH(Values!$D$1:$D$299,LEFT(Chart!A1:A16091,6)&" "&LEFT(Chart!C1:C16091,2)),0) .........
View 2 Replies
View Related
May 1, 2014
If I have a column of data with a bunch of values (which can't be sorted & which is constantly changing so cant be broken into another column and then sorted) what formula do I need to output the second occurrence of a value in that column?
View 3 Replies
View Related
Mar 3, 2014
Attached example file has data with which I need to do the following;
On the tab "part list" I put in the part number in column F. Column C should then be populated automatically by looking up column F in the tab "Lookup" and showing the value found in column B of that tab.
The difficulty with this is, is that when the part number in column F starts with "DN", the lookup value should end with "FD/A" and when the part number in column F starts with "Z", the lookup value should end with "TY/A".
This last part I can't get it to work. I've tried vlookup with a index/match function inside, but it won't work somehow...
View 3 Replies
View Related
Dec 25, 2007
I have used in Index & Match formula in column G as follows :
=IF(ISNA(INDEX(R:R,MATCH(E13,S:S,0))),"",(INDEX(R:R,MATCH(E13,S:S,0))))
The codes in columns R & S are as follows:
******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutR1=RSTU1New CodesOld Codes 2 310100199999V 4101002NEW 51010032270A 61010061100 7101009NEW 81010101104 91010111101P 101010121101S 111010141102 121010151102L 131010181130 141010222203 151010231106I Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The code in column E & G is as follows:
******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG62H62G63H63G64H64G65H65G66H66G67H67G68H68G69H69G70H70G71H71G72H72G73H73G74H74=EFGH621106B| 631100|101006 641101D| 651101K| 661101P|101011 671101S|101012 681102|101014 691105R| 701103|101037 711104|101010 721105| 731106|101031 741106I|101023 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have used a lookup table to calculate the value in column H as follows, but cannot get it to work
=IF(ISERR(VLOOKUP($E62,$M$3:$O$850,3))=1," ",VLOOKUP($E62,$M$3:$O$850,3))
View 9 Replies
View Related
May 7, 2009
I want to create a formula that will use a database table as a lookup for index/match and vlookup type functions.
I have my ODBC connection connecting to a mysql database and am successfully able to run sql queries through excel.
Is there a way to get index/match/vlookup formulas to use the database table as the lookup range?
View 9 Replies
View Related
Feb 14, 2010
I don't want VB code but a worksheet formula.
I have a table full of names and cars sold. The table tracks the person's progress. It is like a diary. It's a simplied version of the DB I am currently working with.
Is it possible to do the following things? I have basically 2 types of questions.
1)When was the last red car sold?
2)When was a car sold irrespective of color?
The answer to each question will be put in separate columns.
I have only included January but I will do this for the entire year of 2010 and also I will do this for all of the names in my database. I think there are about 300.
When was the last time ANDY sold a RED car?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN I
When was the last time ANDY sold a car irrespective of color?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN J
When was the last time BILL sold a RED car?
The answer should be 1/10/2010.
The answer will be put in ROW B COLUMN I
When was the last time BILL sold a car irrespective of color
The answer should be 1/11/2010
The answer will be put in ROW B COLUMN J
I have attached a thumbnail of a sample Database
View 9 Replies
View Related
Feb 28, 2010
I have the following arrangement
PVCu_Woodgrain_DoorF190210300#REF!
PVCu _woodgrain_Door is a drop down list that selects from a number of named ranges.
The Cell that shows (F1) and is called DOORSTYLE with 6 choices F1, F2 ... F6 (I cannot change these to numeric only values)
300 is the 2 previous cells added together. It is called DRSZ1 and will match a numeric value in the named array.
My formula cell with the issue has this formula;
=INDEX(INDIRECT(ARRAYDR1),MATCH(DOORSTYLE,INDEX(INDIRECT(ARRAYDR1),0,1)),MATCH(DRSZ1,INDEX(INDIRECT(ARRAYDR1),1,0)))
I know MATCH(DOORSTYLE,INDEX etc. should be a VLOOKUP to find F1 in the named array, but I just cannot get it right.
View 9 Replies
View Related
Jul 1, 2014
I am very competent using the index match formula and have incorporated this into many spreadsheets to auto-fill for avoiding input errors. The objective I am attempting now is to retrieve from a list of 2800 entries the smallest return on an index match formula.
From the data below if my Index Match formula uses C1 to index and match my return is always a7 (-12.7) the first in the list, where my desire is a return of a19 (-14.11). To complex this problem I often have duplicate amounts that need to be listed when they fall into my parameters. I have figured that the Small function allows me to assign first smallest, second smallest, and so forth.
I prefer not to use vlookup for simplicity and error avoidance issues.
a b c
1 amount Reason for Movement 2250
2 -183.252150
3 -366.612150
4 -61.1 2154
5 -91.652150
6 -4.34 2450
7 -12.7 2250
8 -2.98 2250
9 -2.98 2250
10 -69.372452
11 -0.6 2451
12 -4.49 2250
13 -4.49 2250
14 -4.14 2250
15 -4.7 2250
16 -4.55 2250
17 -4.85 2250
18 -1.41 2250
19 -14.112250
20 -8.47 2250
View 13 Replies
View Related
Aug 4, 2014
How can I modify this formula so it retrieves the 1st matching value (like always), but then the 2nd match, 3rd, etc.:
{=INDEX($A$2:$D$6,MATCH(F2:$F$6&G2:$G$6&H2:$H$6,$A$2:$A$6&$B$2:$B$6&$C$2:$C$6,0),4)} sheet attached: Example.xlsx
View 3 Replies
View Related
Feb 12, 2010
how to go about this, as I always get confused with Index and Match. In my example, in col A I have the Beginning of a period, Column B is the End of the Period, and the amount to be matched is in Column D. Range A1 to E4 is the source data. Below that, starting from A9 to A37 are dates. I would like to match the corresponding value in range D2:D3 if the date in range in A9 : A37 falls between the period stated in A2:B3. I've listed how the values should appear in C9:C37.
View 2 Replies
View Related
Nov 10, 2011
What I want to do is, given the data in col. a, I want to supply a number(range) of strings which can be found in column a, whenever a string is found, which exists in given range, I want to write the string from the Key(col. c) in col b instead of the value from the string that matches a value.
See below for example of what I got working but not exactly what I was looking for
Col. A- Data
Orangegreen
Blackwhite
Blueyellow
Col. B - Result
Col. C - Keys
Orange
White
Yellow
I used
Code:
=INDEX(A1:A3,MATCH("*"&C1&"*",A1:A3,0))
This does produce a result, but not entirely what I am after.
This requires only one string to search for, and will in cell B1 result in a value of 'Orangegreen' as I can only supply one valeu to the function. What I want to do is supply the function with a range of values, and have those values outputtet in colb instead af the value of cells where the result is found.
I tried modifying the function to include a range, but that does not produce a valid result.
View 5 Replies
View Related
Dec 20, 2011
If the weight of a shipment is between a range e.g 3.5kg I need it return the higher cost. For England this would be 9.31.
COSTLook upWeight KGEnglandScotlandIrelandWales1131-3 (KG)7.687.687.688.144454-5 (KG)9.319.319.319.4566306-30 (KG)14.3014.3014.3014.4531315031-50 (KG)22.4222.4222.4223.5551517651-75 (KG)35.1735.1735.1737.04767610076-100 (KG)45.2645.2645.2647.62KGS3.5ORIGINEnglandCost
View 9 Replies
View Related
Feb 17, 2013
In column B I enter a date when a student attended tutoring.
In column C I enter the name of a student attending the tutoring.
In cell D4, I will be entering a student name
In cells D5:D55 I'd like a formula that transposes the dates (if any) that a student attended tutoring. So if a student has their name appear beside the dates ... 14-Feb, 22-Mar, 24-Mar .... then I'd like those three dates to appear in cells D%, D6 and D7.
How to formulate this ... I'm guessing it's going to involve an index-match formula.
View 1 Replies
View Related
Jun 22, 2009
in one column I am looking up the value Yes to return the row number.
=MATCH("Yes",C:C,0) in this case it returns a 2
I want to use this row number in a sum...
i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.
View 9 Replies
View Related
Jun 9, 2014
Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:
EnglishSS1 EnglishSS2 EnglishSS3 Rating
10 20 30 3
20 30 40 2
30 40 50 1
Where my variables would be based on whether a person is under EnglishSS1 and getting a score of <=10, the resulting rating would be 3. I know this is doable by doing a vlookup with a range lookup value of false. However I have roughly 70 column headers and it will be a bit taxing to combine an If and Vlookup statement to address it.
I have attached a sample sheet for reference : Book1.xlsx‎
View 2 Replies
View Related
Aug 14, 2014
What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.
(some examples from the file)
NR2 NR DATE
100707987121951
100702347121960
100707750121960
100707721121960
100702422121960
[code]....
So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.
I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)
View 3 Replies
View Related
