Lookup Value In Row & Return Result X Rows Down
Aug 28, 2007
I am working on a spreadsheet for some packages we are sending to multiple recipients. I have figured out how to get all the weights calculated, now my Mailing Manager has asked me to come up with a calculation for the actual postage amount for all the 1400+ packages...Unfortunately it is not as easy as that would seem.
There are 9 different mailing zones and each zone has 19 weight categories and each category has a corresponding postage amount for the package. So I need to come up with a formula to calculate the package postage amount based on those multiple categories.
For example:
Zone 1:
0-1lb = $2.12
1.1-1.5lb = $2.12
1.6-2lb = $2.21
etc
How do I go about entering in that information from which a formula can determine the proper package postage cost and what formula do I need to use in conjunction.
This is all done within a address list so each package is a row and all weights and zones will be in a separate column.
Package is in zone X and weighs Y pounds therefore the postage is Z. Something like that.
View 9 Replies
ADVERTISEMENT
Dec 6, 2006
I have tried searching for this topic and have been unsuccessful. Perhaps my search was too broad. Either way, I am trying to understand how to search/query a specific data entry among several sheets, and then return corresponding values from adjacent cells.
View 3 Replies
View Related
Dec 30, 2006
I created a drop down menu from a named data range on a seperate spreadsheet in the same workbook. Based on the selection from that drop down menu I want a preassigned number to pull into another cell. I'm not thinking of the correct formula or something, because it's not working. Could it be because it's text? My spreadsheet with the data is as follows:
Bridger to American Fork 051725
Hunter to American Fork 051725
Hunter to Delta 051715
Hunter to Hiawatha 051728
Navajo to Am. Fork w/ front 051725
Navajo to Am. Fork w/o front 051725
The 1st column is the origination/destination and I named that range "freight hauls". Which is what the dropdown menu on another sheet (AP Reconcilliation) consists of. From that, I want the 2nd column number to pull into another cell on the AP Recon sheet. The formula I wrote that isn't working is: =index('AP Reconciliation'!A2:B8,match(A6,'Freight Hauls List'!B2:B7,0),2)
View 3 Replies
View Related
Aug 17, 2007
I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.
View 9 Replies
View Related
Sep 16, 2009
I have a production planning spreadsheet that is updated every week from a database to a new sheet. This sheet is 'Sheet1', last weeks update becomes 'Sheet2'. Every day people annotate and colour cells for various orders, depending on their place within the planning scheme. It's really only the formats that I need to copy across, I could use the VLOOKUP function for the values - So I suppose you could say I'm looking for a VLOOKUP macro/VBA Code which keeps the original formatting (cell colour etc.)
View 2 Replies
View Related
Dec 15, 2006
I have a list of people with SSN, about 3000. This list contains business done by each individual, which can be in all 50 states. I have another list of the same individuals with just their name and addresses and what we call "client number". I need to put the client number with the list for the individual states. See example list:
List of individuals with client numbers
123 John Doe
456 Jane Doe
List of individuals with state business
John Doe AR
John Doe CO
John Doe FL
John Doe MS
John Doe TX
Jane Doe MS
Jane Doe TX
Jane Doe AZ
I need for those client numbers to appear in the column before the names on list with states.
Example:
123 John Doe AR
123 John Doe CO
123 John Doe FL
456 Jane Doe MS
456 Jane Doe TX
View 6 Replies
View Related
Dec 19, 2006
621
532
249
I have these numbers located in column A (rows 1:3)
formula that will return my search result (in these case 532) as well as the information immediately above & below the location of cell that has my search info?
View 9 Replies
View Related
Sep 28, 2009
I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.
So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.
etc.
I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
View 3 Replies
View Related
Oct 17, 2006
I have four columns, A through D
Column C is returning a simple vlookup of A
I need Column D to return a value where C is TDMA return TDMA or when C is GSM lookup column B compare to tab2 (columns A through L) returning column 12.
View 5 Replies
View Related
Feb 4, 2008
I 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.
View 9 Replies
View Related
Jun 5, 2008
I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.
File is attached.
View 9 Replies
View Related
Oct 11, 2007
How do you count the number of columns in a range in order to reach ( find) a specific value in that row ?
View 5 Replies
View Related
Nov 6, 2013
Having an issue with an array formula.
{IFERROR(INDEX('sheet2'!$A$2:$A$14,SMALL(IF('sheet2'!$C$2:$C$14="X",
ROW('sheet2'!$A$2:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.
Originally I did not need to skip any rows, but now I have to skip certain rows. I've tried using my current array formula and skipping the rows not needed but can't get it to work.
{IFERROR(INDEX('sheet2'!$A$2:$A$5,$A$9:$A$14:SMALL(IF('sheet2'!$C$2:$C$5,$C$9:$C$14="X",
ROW('sheet2'!$A$2:$A$5,$A$9:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.
Can this still be done with an array formula? I need to skip rows 6, 7, 8. Not sure what I'm doing wrong but no names are appearing. Sheet 1 looks like this with "Job Titles" starting in A2 on sheet1.
Job Titles
Dept.
1
2
3
4
Claims
CSI
Outb Calls
CSI
Notifications
CSI
Evaluator
CSI
I tried a new formula in cell C3
:=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(ROW(Sheet2!$C$2:$C$14)=9,
IF(Sheet2!$C$2:$C$14="x",ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1)))),C$2),1),"") entered as an array.
I should see the name "C. Pepper" followed by "L. Smith" when I copy the formula to the right into D3, "J. Rast" would be skipped as these are the rows not being picked up, but nothing is showing.
Sheet 2 looks like this with "Staff Name" starting in cell A1:
Staff Name
Mgr
Claims
Outb Calls
Notifications
Evaluator
A. Hawkins
Adams
[Code] ..........
View 3 Replies
View Related
Oct 13, 2008
I am having a problem writing a macro that sources one of its values from a cell that contains the following formula =LOOKUP(J9,{0,80,85,90,95}, {"0%","40%","60%","80%","100%"}).
The above formula correctly returns '80%' but when I use this cell as an input for another formula Excel reads it as '0%'. Can, and if so how does, one use the resulting value of the lookup function as a fixed value for another function?
View 5 Replies
View Related
Jun 14, 2009
i am trying to make report for Mycompany sale
when i sert the date of Sales give me all the date of payment for this invoices
here is small example
ABCDE1Invoice DateInvoice NumberPayment Date
Invoice Date22009/06/011112009/06/05
2009/06/0132009/06/011122009/06/0542009/06/011132009/06/0452009/06/011142009/06/0262009/06/021152009/06/1072009/06/021162009/06/0682009/06/021172009/06/1392009/06/021182009/06/13102009/06/031192009/06/13112009/06/031202009/06/05122009/06/031212009/06/04132009/06/031222009/06/10142009/06/031232009/06/10152009/06/031242009/06/05
I WANT
when i insert the date in E2 to give me all the date of payment in this day in range under this cell
View 9 Replies
View Related
Jan 26, 2014
In my spreadsheet I have a column (C) that consists of 20 rows. Within this column a calculation is done with only one value (currency) that appears based on the calculation criteria. All other rows are 0 (currency) and the correct calculation can be in any of the 20 rows.
In an adjacent column (say E10) I want the appropriate value to be entered from column C.
View 3 Replies
View Related
Jul 7, 2007
i'm trying to return a value thats offset but the position of the value may change each time data is imported. i've attached sheet displaying problem, as i can't explain clearly. i need to lookup the value in column A (yellow), then find and return the total percentage value in column C (blue). its always 2 columns across but the problem is it could be anything from 2 to 5 rows down.
View 6 Replies
View Related
Oct 11, 2013
I want to scan the envelopes coming into our postroom and look up the results within excel. I would then if I match the name be able to print out the details e.g. name floor they are located on etc..
View 3 Replies
View Related
Apr 6, 2007
I have a data table, A1:J22. The purpose it serves is to align and compare data from other tables, so the data contained within it is all cell references and contains many #N/A values. Also, and most importantly, not all of the columns are populated with data. The first row is a text header row and the first column is numeric and adjusts according to the value in A2. I have an additional column at the end of the data table, yet apart from it, which contains an array formula to provide me with the maximum value of each row and to circumvent the #N/A's, =MAX(IF(ISNUMBER(B2:J2),B2:J2,"")). I am trying to write a formula that will return the corresponding "header" of the result of this formula. Everything I have tried thus far, primarily variations of Lookup and Index/Match formulas, seemingly defaults to the header of the last column containing data.
View 3 Replies
View Related
Aug 1, 2007
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list.
For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc
... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
View 9 Replies
View Related
Sep 18, 2007
Is it possible to get the location of the result cell of a lookup result. For example, instead of showing the cell value it shows the cell address of the results of a lookup. You see I have this Summary Report of a payroll system. Everyone doesn't need to pay social charges, except two people, "person1" and "person2". What I want to do is to first look at the names in the report to see if one of those people are listed in the summary(this can be done by lookup, I think). Then go to a different column on the same row as the person. So, for example, "person1" is in a14, then the macro will select the cell say... g14.
View 6 Replies
View Related
Dec 21, 2007
I have three columns. Column A=" Name", Column B=" Date" and Columnc="Amount". I need to find and go to the correct amount based on a certain name and date as criteria.
I was going to see if it was possible to use this =INDEX(C1:C1000,MATCH(1,(A1:A1000=D1)*(B1:B1000=E1),0)) with a hyperlink, but I can't even get it to work without a hyperlink. I also tried this to find the match= SUMPRODUCT((A1:A1000=E5)*(B1:B1000=EE6)*(C1:C1000)). I found it on a website(I'm not sure how it works.) My next shot was going to be with multiple criteria vlookup, but I can't get anything more than a plain vlookup to work. I'm using excel 2007 non-commercial use.
View 4 Replies
View Related
May 28, 2008
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.
I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
View 4 Replies
View Related
Nov 22, 2007
I am using an array formula (on the RIM Detail Sheet) to assign a weighting to a value taken from Irregular securities sheet; though the values that this formula need to return don't seem to be correct.
I am specifically referring to row 111 of RIM detail sheet. As the corresponding value on the Irregular securities sheet is > 50%; the array formula should return a result of 2 but instead its returning 10.
View 8 Replies
View Related
Dec 10, 2013
So basically I am looking to build a formula that would take this information and return a text value.
if it could work anything like this sumproduct formula to bring back text that would be awesome:
=SUMPRODUCT(--(A1:A10="7654321"),--(B1:B10="ABCDE"),--(C1:C10="3"),D1:D10)
But instead of a formula that returns a sum of values, the return is a single value. The three criteria will always be unique. There will never be a case where there is a duplicate of all three.
View 2 Replies
View Related
Mar 24, 2008
i need a formula that looks up a range on sheet 1 coloum a and returns the value in column b, unless the value in column c is less than 5 letters long/ or not equal to a time format if this is the case it should return the value in column c
[code]
=IF(LEN(VLOOKUP(A6,'look up'!A6:C18,3,FALSE)>6),VLOOKUP(A6,'look up'!A6:C18,3,FALSE),VLOOKUP(A6,'look up'!A6:C18,2,FALSE))
[code]
this is my effort but it returns value in the the middle every time
View 3 Replies
View Related
Dec 9, 2013
Been working on this problem for a while, I need to have a vlookup (or other function) return locations based off of a concatenate (for unique identifiers). Only issue is that the concatenate formula creates doubles which returns the same location for each vlookup. I have attached an example file.
View 2 Replies
View Related
Oct 14, 2008
creating a formula that will give me a result of either "W" or "L" depending on the amount in corresponding cell.
View 9 Replies
View Related
Dec 16, 2009
I need to figure out a formula that will look across a row and once it finds the criteria specified, it will return a value going down the corresponding column. I have attached a file showing the example.
View 3 Replies
View Related
Aug 5, 2008
I have this formula in C7:
=VLOOKUP(A7,A$20:C$76,3)
The Table Array contains a list of 12 months, and there will be a match to the Lookup Value in A7 each month. I would like C7 to reflect the last completed match in the list (it will be updated each month). So, when June is the last completed month, C7 will return the matching amount from the Table Array for June; then when July is completed, I would like C7 to reflect the matching amount for July.
View 9 Replies
View Related
