I need help using the vlookup formula when there is 2 values that needs to be looked up to find the result.
for ex:
I need the result from Column "C1" which is the price from sheet2 by looking up the color, navy which is in Column B1 on sheet2 & looking up shirts which is in Column A1 on sheet2.
I am trying to write a formula that is based on a vlookup that will return multiple values. Attached is a spreadsheet that is an example for what I am trying to do. On the download tab it is an example of a general ledger. On the total tab I want to enter a GL# and have it list all the entries for that GL number. I know I can try an array, but I have not been able to figure that out.
View 6 Replies View RelatedI want to create a Pivot table, and is now working on the data sheet. In this, every salesman have one row each day containing their sales result that day.
I have data in 3 columns. In column A the date, column B contains the Name of the salesman and column C the sales result that day. Now I want to update column D with each salesman's best result. I want each row to contain the top result for that particular salesman.
Date | Name | Result | Top result
1/10 | Pete I | 11.111 |
2/10 | Pete I | 19.100 |
3/10 | Pete I | 23.408 |
4/10 | Pete I | 16.774 |
1/10 | Sara G | 10.884 |
2/10 | Sara G | 10.739 |
3/10 | Sara G | 36.469 |...................
creating a .Find code to search a range on a sheet and then display each result seperately in a msgbox. An inputbox will be the value .find searches the range for.
the msgbox needs to be a vbyesno
VB:
'SEARCH CODE
Dim myItem As String, myRNG As Range, NewLoc As String
Dim Found As Range [code].....
Basically, what I want is to find the earliest EPSI1MN and its associated EPSI1YR and EPSI1NE, within the same period of EPSI1YR and then store the results in a separate sheet. This process is to be repeated for 12 years of data, covering more than 4000 companies.
View 3 Replies View RelatedI have the following formula but would like the result to be blank if it can't find anything. I know I need to use the iserror function but I don't know how to use it.
Can anyone help me?
=VLOOKUP(CONCATENATE($A$3,$B$3,$A7),Sheet1!$A$6:$R9987,$A$1,0)
I am using the find method to search column headings, and based on the results copy the column to another worksheet. Everything works fine except if I have a mixed text and numeric string in the cell, for example DT35. In this case the macro doesn't copy this column. I have attached the spreadsheet. CTRL - A will run the macro. The macro calls a form with checkboxes, captioned using the values in the worksheet titled "Set-Up". If a value is found in the column heading the checkbox is set to true, then when the "Copy Selected Columns to Final Sheet" button is selected the columns are copied to the "FinalSheet" worksheet. I tried using xlPart instead of xlWhole, and this works but I need to search for exact strings so xlPart isn't a great work around.
View 4 Replies View RelatedI have two workbooks Raw File and Lookup_Test, I want to find the value of Column A from Raw File, in Column A of Lookup_Test, if the value if found then i want the value in Column D from Lookup_Test, to be copied to the Column B of Raw File .
Well its like using vlookup , however i am trying to avoid using Vlookup and find any other way of doing the same.
I'd like to search for text and get the column character where the cell with the found text. The combination the two 'demands'/formulae into one are giving me problems. And the fact that the column character can exist out of two characters I cannot solve.
I can get the column number with HLookup right? and it gives me the column number, not the character. I can solve this partially (and ugly even more) with =Char(((Hlookup etc)+64 (letter before A))).
Dim myDynRange As Range
Dim myLastRow As Integer
Range("H:H").Select
myLastRow = Range("H:H"). CurrentRegion.Rows.Count
myFirstCell = Cells(Counter, 8).Address
myLastCell = Cells(myLastRow, 8).Address
myDynRange = myFirstCell & myLastCell
im having a problem with the last line
myDynRange = myFirstCell & myLastCell
my eventual aim is to do an average on this column with a line like
ActiveCell.Offset(Counter, 1).Value = Application.WorksheetFunction.average(myDynRange)
the reason why my range declaration is so strage is because i was trying to change the dimensions of the range with an offset function because i had many ranges to average but the offset function didnt want to work. this way goes like this....................
I am using VLOOKUP to find the size of a cam to be installed in a tablet press, based on the product code it will be running.
The array has two columns: (W) Product Code, (X) Cam Size.
Array: W4:X437
The user selects the Product Code from a drop-down list in cell E5.
The resulting Cam Size is displayed in cell E7. The VLOOKUP works fine.
=IFERROR(VLOOKUP(E5,W4:X437,2,FALSE),"")
Occasionally, the cam size has to be updated. The user would then select a new cam size from a drop-down list in cell E9.
I have a "Update Cam Size" command button.
What I need to happen is for the value in E9 to replace the value in the array that is displayed in E7. Obviously, I have to know the location of the cell in the array, but I can't figure that part out. I've tried ADDRESS and MATCH functions, but it comes back with "#N/A" Value not available error.
=ADDRESS(MATCH(E7,W4:X437,0),2)
Average.xlsxHi
I have changed the attachment to use CSV's
I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.
What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.
Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.
Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1
I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)
I have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.
I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).
I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied....
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below
Red
Green
Blue
Orange
Yellow
Result
2
4
3
6
1
[code]...
I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers
I am using the following formula (which I need to adapt) to enter a value in a cell C30.
C30=IF(A30>0,A30,0)
I will need to enter this formaula in column C rows 1 to 300.
The problem is that, in addition to A30 needing to be >0, I only need to enter a value in C30 if a value (>0) is found in column B (B29 going back to B1) before a value is found in the A column (A29 going A1). If a value in the A column is found first then I need to enter "0".
For example, if A30 is >0 and the previous A row A29 is >0, I want to enter 0 in C30.
Another example, If A rows A20 to A29 are 0 and B19 >0, then I want to enter A30 in C30.
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
Spreadsheet 'Raw Table' has many, many rows, and a dozen columns of raw input data. Each row has date, number, and text fields.
Spreadsheet 'Dashboard' is a somewhat simple one page summary of one day's worth of data entry from Raw Table. Dashboard isn't a simple table, but more form-like in appearance (not sure if that matters). Essentially, the values from Raw Table are spread out in different locations in Dashboard, and not just in a row.
The Dashboard fields gets their values from Raw Table based multiple criteria, the most important being a manually inputted date entry at the top of the Dashboard. The other criteria are simple numbers (ie, 1, 2, 3, 4....) built into the formula. So a set of criteria for one particular cell in the dashboard could be: 4/6/2014, 1, 3. Those 3 criteria are unique identifiers: there is only one match, ever. If those criteria are met, then a value from a specific spot in that row from Raw Data is placed in the dashboard field.
Here is one formula example for one such field in Dashboard:
=INDEX(('RAW DATA'!$D$1:$D$20000),SUMPRODUCT(('RAW DATA'!$A$1:$A$20000=$D$1)*('RAW DATA'!$B$1:$B$20000=1)*('RAW DATA'!$C$1:$C$20000=3)*ROW('RAW DATA'!$D$1:$D$20000)))
The first sumproduct criteria is a match for the date in Raw Data's column A with the manually inputted date in Dashboard located at D1.
The second criteria is match for the value in column B with a value of 1. The third criteria is match for the value in column C with a value of 3. (Again, this is an example. The 1,3 set can be any specific combo from 1,1 to 8,8. Thus, a day's data can have no more than 64 records of a dozen date, number and text fields.)
The Index function is obviously array,row,column. So this example index function results in the contents of column D in the row that the sumproduct function generates.
This dashboard spreadsheet works perfectly everywhere except four fields (out of 100+ instances of the same formula). In the faulty spots, the value that the index function is returning in Dashboard is basically the contents of column D in Raw Table in the row that corresponds to the place the formula is located in Dashboard. It's almost like there's an error in the formula result, and the index is defaulting to the row number in Dashboard. But I've tested this 8 ways til Sunday. I can even straight-up copy these four faulty fields to another part of the dashboard, and the results are magically correct. And I've checked to see that the actual sumproduct function result is returning the correct row for these fields. I don't have an answer as to why it's defaulting to the formula row number in just these four locations, and not the row value that the sumproduct function is correctly producing.
I've thought maybe it was a data type problem in Raw Data, but that route produced a dead end. It has to be something with the Dashboard. I'm going to start over with a new Dashboard spreadsheet, and see what happens. One thing I did was, early in development of the dashboard, use ctrl-shift-enter in the first faulty field, then went back and changed the formula wholesale to something different, and copied that cell to (at least I think) the three other faulty spots. But if I went back and completely changed the formulas and did away with the brackets, the cells should be fresh, no?
I have set of column data A & B In those A contains Serial No & B contains Ticket No...
I am looking for formula to solve the function by those conditions... almost get done by countifs functions.
1.If Serial & Ticket No Only Once result should be "FIRST TIME LABOUR ONLY"
2.If Repeat Twice For The First Serial No And Ticket "Labour Only", For The Second Serial No And Ticket "Labour & Parts"
3.If Serial No Twice But Ticekt No is different for both serial no Result Should be "PART USED 1 OF 2"
4.More Than 2 Times Serial & Ticket No Repeats" Result Should Be "CCI"
I use this code to look for values like V1/V2/V3/V01 in my column. If the cell contains the value, nothing happens, if not, the whole row is deleted.
View 2 Replies View RelatedI'm trying to use Advanced Filter to generate all the unique values within a list, only at times I want to Autofilter the data, then use the advanced filter to determine all the values of the slimmed list. For some reason, this works fine on column A, only returning the values that begin with a 1 when the column is filtered for all values beginning with 1. However, when I run the advanced filter on column 2 with column 1 filtered to all beginning with 1, all values all returned regardless of whether column 1 criteria is met. Either way, after the advanced filter, the autofilter has been removed.
View 7 Replies View RelatedI am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
I have below table and want to get new order quantity if the closing stock of a particular product is less than or equal to the ROL after viewing that a previous order of the same product has not been placed within the lead time of that particular product even the closing stock is less than ROL.
Product
Date
New Order
[Code]....
- 1st it match the product with the relevant one
- Then compare closing stock parameter
- Then finally look up the previous order and compare it with relevant lead time
I have been trying to find a formula for this but I have yet to find one that works for me.
Data;
English - United Kingdom, Czech, Spanish
UK - United Kingdom
Swedish
English - US
French
US
France
German
Portuguese
+8000 more various values
I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".
I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.
I have 4 text boxes in a user form where user type numbers. At the end i have another text box(yellow one) that adds these numbers using this code.
[Code]....
In another text box(grey one) user types another number. Then using this code
[Code] .....
I try to add the values of the two previous text boxes. I get wrong result when i use decimals numbers and i can not understand what is wrong.
Attached File : add text boxes.xlsm
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.
I have a column between the years that calculates the percent of gross revenue for the specific department/cost.
I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.
The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
I need to input a formula that will produce a "yes / no" result if a "0" value appears consecutively six or more times.
Using the date below (ranges A1:Q1, A2:Q2 and A3:Q3), I need cell R1 to read "yes", as there are 7 consecutive "0" values. Cells, R2 and R3, however, need to read "no" as there are less than six consecutive "0" values in rows 2 and 3 respectively.
A
B
C
D
E
F
G
H
I
J
[code].....
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data.
In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.
I'm trying to find a formula that will give me a count of all programs by month that have an evaluation result. So, in column A1:A24 I have a list of programs with. In cell A25, I have the total number of programs. In column B1:B24, only 7 of those programs have evaluation results, some of the rows are blank.
I know that I can get a sum of the programs by month, but how do I only get a count of those with an evaluation result? What formula can be used to get a year to date (a running count), not a count based on 12 months? Meaning that we would need a year to date for each month, so currently for October to June (based on fiscal). If we based it on year to date, then I think we would have to divide be 12 and that would make the results inaccurate.