VLOOKUP And SUM Formulas: Pick Up Another Value In Column 9 And Should Add Value In Column 7 And 9 And Return It
Apr 6, 2007
=IF(ISNUMBER(VLOOKUP($E8,Su!$D:$K,7,FALSE)),VLOOKUP($E8,Su!$D:$K,7,FALSE),)*$I8
In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.
View 8 Replies
ADVERTISEMENT
Apr 5, 2014
does it not like dates? i have columns of data and i wish to search it for each month and then print a months worth. here is part of the data. maybe vlookup does not like dates? i tried putting an index column on the left, 1,2,3,4... but it still would not pick up "Jun"
View 8 Replies
View Related
Feb 8, 2010
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.
But I want find from Given column number and return value from given left column. For Further detail see the Attached file.
View 3 Replies
View Related
Apr 4, 2014
I want to look up a value in the third column of a table and return the value in the first column of that row on the same table. What do I do?
View 2 Replies
View Related
Dec 12, 2013
I have data like this:
NameRank
pietro30
fabri35
mauri60
andrea45
luigi50
anna90
federica100
julia120
Now I want to pick names from column A with rank difference between name should equal to 30.
Expected result
NameRank
pietro30
mauri60
anna90
julia120
View 7 Replies
View Related
Mar 30, 2012
I'm trying to get all subjects that are "pass" per student #.
Student #MathScienceHistoryStatisticsPhysical EducationHealthPhysicsEconomicsTrigonometryEngineering
5200309PASSPASSPASS5200306PASSPASSPASSPASSPASS5200307PASSPASSPASSPASSPASS
5200305PASSPASSPASSPASSPASS5200257PASSPASSPASS3046109PASSPASSPASSPASS3045539PASSPASSPASSPASS
3045613PASSPASSPASSEnter Student #
If I type student number here the result will be shown below with all subject passed.Subject Passed
View 9 Replies
View Related
Jul 6, 2007
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 3 Replies
View Related
Oct 7, 2008
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
B1 poduction_order_number
A2 L1
A3 L2
A4 L1
B2 505212
B3 504234
B4 505663
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View 2 Replies
View Related
Jul 14, 2009
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?
ID# LOCATION
23 SERVER1
34 SERVER4
23 SERVER2
13 SERVER1
17 SERVER3
34 SERVER1
View 10 Replies
View Related
Jul 7, 2014
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
View 3 Replies
View Related
Jan 31, 2008
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
View 9 Replies
View Related
Aug 27, 2012
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
View 3 Replies
View Related
Mar 23, 2009
Is there a formula or function that allows me to always select the lowest (not min) actual lowest in a column? For example, I have a formula in A1 and I need it part of it to always reference the bottom of a certain range (i.e. A5.A200).
So one day the lowest would be A7 but a week later it could be A8. I don't want to have to keep changing the formula in A1 to find the lowest value. I will be adding data on a regular basis.
View 6 Replies
View Related
Dec 24, 2007
I've got a list of names in a column in excel, what's the easiest formula to pick a random name from that list? (guess I can use f9 to randomize)
View 6 Replies
View Related
Mar 17, 2009
The attachment shows a ledger that I am trying to build. My goal is to automatically add the Ledger balance(G) at the end of each day to the Investment cash balance(J) column. Some days there are multiple Ledger transactions, other days there are none. If there were no Ledger transactions on that day I would like to use the last recorded Investment cash balance. (ex. on weekends there will be no transactions)
Note: I want exactly one balance, the ending daily balance, per day on the investment side of the worksheet.
I have struggled with this all afternoon. My goal is to make this as simplistic as possible for two reasons. 1) I am not a master programmer by any means. 2) I want this file to open on any computer the has a MSExcel on it without have to install any addins.
For these reasons I am thinking it would be best to stay away from macros, which I am minimally proficient at creating.
Let me know if you have any good tricks to solve my problem. If macros are the ONLY or absolutely the BEST way to do this, then I guess you can twist my arm an we can go down that avenue.
View 14 Replies
View Related
Feb 12, 2014
As per attached file, there are altogether 4 sub-files and 1 master file.
I'll need the code to automatically pick up the top 9 revenue(highlighted in Green) and top 9 EBIT(highlighted in Yellow) by values in Column N from 4 sub-files and paste to the master file.
Attached File : Example file1.xlsx‎
View 9 Replies
View Related
Mar 31, 2009
I have a column of numbers and I want to copy numbers, with a constant periodicity, and paste in a new column. In the example below, I copy values from column A and past in column B, picking only every third value:
A B
1 1
2 4
3 7
4 10
5
6
7
8
9
10
View 7 Replies
View Related
Jul 2, 2013
I have some vba code that opens up notepad and populates it with data from Access 2010. Everything works great including the notepad function, the database, and how the data is stored and displayed in the table/form.
The problem: I want to display the service name not the ID in notepad
I passed the field that I want to include in notepad as a string. However, it returns the ID and not the name of the person.
I don't want to change anything in the form or field property because it works perfect as is.
Is there a way to pick which column to display in the Email by vba code?
Column 0 = the ID and Column 1 = the name
Code:
Me.fieldName.Column(1)
^^That doesn't work for me
Here's my code (it works)
Code:
Private Sub cmdNoteAccept_Click()
Dim strCode As String
strCode = strCode & Me.Servicer & ": "
strCode = strCode & Format(Me.DateCreated, "MM/DD/YYYY")
strCode = strCode & "Insert message here"
Shell "Notepad.exe", vbNormalFocus
SendKeys strCode, True
End Sub
View 3 Replies
View Related
Aug 20, 2008
I receive hundreds of part numbers to quote daily. I send out RFQ's to my suppliers and they respond with pricing. I gather all the data into a similar excel sheet below and assign the wining supplier.
in automating a task that will compare the group of data for best pricing and populate a "WIN" code in column "A" if that supplier was chosen for the that qty pricing.
Here is the excel example.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI22=ABCDEFGHI1Scenario #1 - Supplier chosen for best pricing based on column "E" 2WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST33 123ALL90110510107.54 123FO3511258107.755 123RO501958.51086WIN123AR651858.751087 8 9Scenario #2 - Supplier chosen for best pricing based on column "G" 10WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST311 123ALL90110510107.512 123FO3511258107.7513WIN123RO501958.510814 123AR651858.7510815 16 17Scenario #3 - Supplier chosen for best pricing based on column "I" 18WINNERPART#VEN IDBRK1COST1BRK2COST2BRK3COST319WIN123ALL90110510107.520 123FO3511258107.7521 123RO501958.510822 123AR651858.75108Sheet1 [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.
View 10 Replies
View Related
Jun 9, 2007
I have two columns in excel. Column “A” where I have different group names column "B" all the different numbers and column “C” where I have ranked based on column “A”. I need a formula that would pick a number that is ranked 51 with specific group. Currently I’m using below formula (where MC1 represents one of the groups)that is working great unless there is no tie, however if there is a tie for rank 51 and let say two numbers are 22 and 22 the formula will return 44.
SUMPRODUCT(--(Terr!$Q$16:$Q$518=MC1),--(Terr!$AC$16:$AC$518=Main!C69+1),Terr!$Z$16:$Z$518)
View 4 Replies
View Related
Oct 14, 2008
See attached. This is a report that I have to do every month. I cant work out a formula for it. What I need is a formula that will pick out which is the largest Figure of column A,B,C,D for each row and put the corresponding header in column G. i have manually put these in. Would anyone know a formula i could use for this.
View 3 Replies
View Related
Dec 10, 2013
See data in the attached image in Col A thru Col D. In Col E, I want to populate the max value for the same part # from Col C or Col D depending upon value in Col B.
I have included a sample of the expected results in Col E for illustration purposes.
For example Row 3,5,6 are all part # 1 with Col B ="Y", suggesting they are interchangeable parts, therefore, I would like the formula to have ability to pick 10 (since its largest between 3,10,0) from Col C.
Capture5.PNG
View 1 Replies
View Related
Dec 2, 2009
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!
View 2 Replies
View Related
Jun 16, 2013
In my spreadsheet the formula
=count(k:k)
gives 8 which is correct.
But I know ONLY that the column is 11 (that is 11 th column). Then how do I write the above count formula wilthout using column letter k.
View 4 Replies
View Related
Jun 7, 2014
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
View 2 Replies
View Related
Feb 28, 2014
I have a spreadsheet of several thousand named items (in column B) with values associated with them in column A). The "name" field is a string of several alternative names for the item.
I have a list of ~50 items that I am trying to find the values for. Each uses one of the alternate names.
What I want is a function that will return the associated value in column A when one of my shortlist names is found.
For example, my big spreadsheet looks like:
AB
1dallas, DFW. 12345
2Atlanta, ATL, 23456
3Boston, BOS, 34567
4Chicago, CHI, 45678
5Los Angles, LA, LAX, 56789
And my list looks like
Atlanta
CHI
I want the output to look something like:
Atlanta2
CHI 4
Is the feasible? IT seems like there should be a way to do it....
View 2 Replies
View Related
Feb 7, 2014
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
View 3 Replies
View Related
Jun 5, 2009
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies
View Related
Feb 19, 2009
I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.
In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.
View 2 Replies
View Related
Dec 16, 2013
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.
View 2 Replies
View Related
