I am looking to do lookup list using the offset function. Scenario: I have 5 dealer groups, each with there own dealers. Each dealer has a list of customers. Once I select the customer it will bring up a list of their vehicles. I am stuck on selecting the customers and vehicles once the dealer group and dealer are selected.
View 1 Replies
I have the following formula in a Data Validation List:
=OFFSET(B8,MATCH(J2,
(CHOOSE(B7,D1Array,D2Array,D3array,D4Array,D5Array,D6Array)),0),1,COUNTIF(CHOOSE(B7,D1Array,D2Array, D3array,D4Array,D5Array,D6Array),J2),1)
In B8 it is the formula =VLOOKUP(B7,LookupTable,2,0). and the lookup array is this 1$H$112$J$113$L$114$N$115$P$116$R$11
So the value returned in B8 will be a the start reference for the OFFSET function. This works but it retuns blank values and not the values in the arrays.
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
I have been writing a speadsheet using drop down lists and the Lookup function to link names to body weights. When using the dropdown list it displays for one name the incorrect weight. All the rest work correctly but one will not. From the list below when I select the name White it displays '68' rather then '80' as it should. If I change the name of White to the number 4, it will act correctly and display the weight '80'. It seems rather bizare.
This is the function that I am using
=LOOKUP(C53,Weights!B2:C9, Weights!C2:C9)
Pilot
Jones
78
Smith
95
[Code] .....
I'm working on an invoice generator, and I want to add a "lookup by phone number" feature. I've got an invoice database with invoice info stored horizontally for past invoices. On a separate sheet, I want to be able to type in the phone number in range ("O11:Q11") and have a list of invoice numbers and dates matching that phone number appear in Columns R and S respectively.
Here is some workbook info:
Invoice generator is in 'phonelookup'
Invoice database is in 'invoice database' and the table is called CompInvoices
In CompInvoices, Invoice numbers are col1, dates are col2, and phone numbers are col7.
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedI have a set of data
Col A, B, C
A = Date, B = Interval e.g 08:00, 08:30, C = Number
My aim is to lookup to A+B in order to retrieve C
I have tried and offset within a lookup and upto now have been unable to proceed, I have attached the formulae I was using;
=HLOOKUP($I$4,Sheet2!$A$5:$A$6,OFFSET(B5,MATCH(H5,Sheet2!$B$5:$B$6,0)*0,1),FALSE) - returns the date value
I am trying to use offset & lookup, to return a cell x rows below the lookup value. I am able to get the correct result when I use the lookup and offset functions separately. When I nest the lookup function within the offset, I get an error.
View 6 Replies View RelatedI have a range of numbers in row 8 all of which are unique, then another range in row 20 (there are numbers in between but I'm not worried about those). I'm trying to get a code or formula that will find a user defined number typed into c30 (which can change) in row 8, then scroll down to row 20 (same column) and sum all the values from b20 up to and including this point (along this row). The number inputted into c30 will always exist somewhere in row 8.
I can't work out if this is possible using lookup/offset/cell/sum/other functions, figure it may need VBA.
I have a large array of cells that go from A1 to A2500. In VB, I need to take a given date that resides in a cell, say 1/6/2006 in C2, and look for a match of it within this large array. When the match is found, I need it to look in the column to the right (IE, from A5 to B5) and assign the value there to a Cell (C1).
Could someone provide an example of the code I was use in VB for this? I'm having a lot of trouble with it.
I have a worksheet of data that I am trying to reference to. I will have the data as a "dump" on one worksheet, and lookup from that sheet. I've attached an example of the data dump. I need to essentially VLOOKUP the value (i.e. 054_6425) and then count over 3 columns, and down two rows. I've tried INDEX MATCH, but can't get that to work. Can you get VLOOKUP to count over and down and return a value? File "example" is attached.
View 2 Replies View Relatedi'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 RelatedI am referencing a value in column A of Sheet 1. I am looking for that value in column B of Sheet 2 and would like to return the value of the cell that is 1 row below and 4 columns to the right (column F). I need to do this multiple times.
Can I use a VLOOKUP/OFFSET combo or Index/Match?
I am writing the formula in column H of sheet 1.
The Vlookup portion is already working but I need the value located one row below that one.
here is what I have, which return the value itself, I cant seem to incorporate the offset function ion VBA:
Cells(x + 1, y) = Application.WorksheetFunction.VLookup(TempName, Worksheets("Data").Range("F1:F100"), 1, False)
In the attached workbook, the following formula appears in column B of the "dropdown" tab. I want to basically do a two criteria lookup, using the values in column A and the value of cell B1, which is a dropdown based on a dynamic range consisting of the company names on the Discounts tab.(starting with cell E1, extending rightward) To make this easier for testing purposes, I'm curretly using cell E1 instead of B1, which is just text.
It seems like this ought to work, but somehow it doesn't:
=OFFSET(Discounts!$B$1,(MATCH($E$1,Discounts!$C$1:$AA$1,0)),(MATCH(A5,Discounts!$B$2:$B$200,0)),1,1)
I am having a heck of a time coming up with the proper combination of functions to return data to my worksheet.
Attached, you will find a screen shot of the pages I am dealing with.
Here is what I am trying to do:
On the page named Update Master, I am trying to put formulae into the highlighted cells C2:C11. Each cell will contain a formula to MATCH the team name in UpdateMaster!A:A with a team name in the chart found on worksheet WEEK ONE. Then, I want to return the value found 8 cells down, and 1 cell to the right of that MATCH. Here is a specific example:
In cell Update Master!C2, I need a formula that will MATCH the value in Update Master!A2, , with a value on the WEEK ONE! worksheet. (In this case, 2EZ. After locating the match, I want to return the value found in the bottom right of that players weekly score box. (WEEK ONE!C9...in this example).
I can't make a direct reference to the data...because the value in Update Master!A:A will change after each week. The team names will be sorted in alphabetical order after being sorted by the value found in Update Master!AE:AE...(each player's accumulated score).
I have tried so many combinations of MATCH, LOOKUP, OFFSET, INDEX, ROW...etc., and I keep getting VALUE# or NA# errors at some point in the computation. Maybe one of you can point me in the right direction.
on sheet 1
I have a list of race car numbers (20K, 15W, 2) in A1:A50 (may be more or less than 50)
Next to them in B1:B50 are the point values they earned for a night of racing. 100-97-94 etc.... these are points they have earned for a night of racing that week.
in C1 I have how many column over I need to write to (ex. 4 for Column "D" on sheet 2)
On sheet 2 I have all the total for each week.
example
A1 B1 C1
Car 4/22 4/29
20K 94 90
15W 97 100
......
What I need is some code to go down the driver list on sheet1 and write their points in colum D on sheet 2 when it finds the appropriate car number. If the car number does not exist then add the car to the bottom of the list (A50) or whatever, and write the points 4 columns over.
I have a data range set up in a step chart formation. This means there is a "Name" to the left and a time line of "values" to the right in quarterly increments (3 months at a time). Row 1 has the first month of each quarter and should be used to find the start date. Row 2 has the last month of each quarter and should be used to find the end date.
I need to find the first value from left to right and return the date directly above it in row 1.
Then....
I need to find the last value in the step chart and return the date directly above it in row 2
Example
Start Date..A...B...C....D...E...F
End Date....U...V...W...X...Y...Z
Value-1...........5...2....7........
Value-1
Start Date: B
End Date: X
Duration: = B - X
The reason i need this information is to create a Gantt Chart.
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.
I have 2 columns of data (col A is time, col B is a value), in a range A4:B1000 (can be less, but the biggest the range will get is to B1000). Example:
A / B
4 10:37:55 / 91
5 10:37:56 / 92
6 10:37:57 / 93
7 10:37:58 / 100
8 10:37:59 / 105
9 10:38:00 / 110
10 10:38:01 / 111
11 10:38:02 / 115
12 10:38:03 / 120
Im looking for a way in VBA to look for a time entered in cell C1 (example 10:38:00), find that in column A, then return the average of values in column B that are offset 271 - 300 rows. This is entered in D1. So in my example, 10:38:00 is in cell A9, return the average of B280:B310.
The main worksheet in my workbook contains lists of suppliers with unique Vendor Numbers, of which there are approximately 4,500 rows. There is another worksheet which holds approximately 6,500 entries I need to bring data from that sheet to the main sheet using the Vendor Number and the information that is held on 4 columns unique to that number.
The information is a classification system that comprises of 4 columns holding a mixture of numbers and text.
Worksheet 1 has the Vendor Number in Column A and the Classification destination starting in Columns Q, R, S & T.
Worksheet 2 once again has the Vender Number in Column A, and the Classification information in Columns C, D, E & F.
What I need guidance on is how to build the formula to copy the information across, or direction into what other method I should be looking at instead.
In the attached spreadsheet you will see the table of data in cells e6:L16. In cell N6 i am doing a hlookup to find the date in the table of data that is closest to the date in cell A4. The formula in cell M6 is where i am having the problem and i was trying to pull in data that is 4 columns to the left of where the hlookup date is in cell N6. The problem i am having is when i input any date higher than 06/30/07 the Index formula does not display the correct date. What is confusing me is that the Hlookup formula in cell N6 still works with higher dates but the index formula does not.
View 6 Replies View RelatedI am trying to use the vlookup function together with an offset function but i am not getting it to work properly.
The situation is as follows:
I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.
I have though of using the offset function, but i cannot figure out how to make this work.
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies View RelatedI'm trying to use an offset from a Named Range as my source cell in a vlookup but get a -1004 Object not defined error. When Debugged the Range.Formula appears to be blank when queried. Using Record a Macro the Reference to a named range worked fine but I could not test for an offset or the use of the range reference. What can I do to use to make this work
Range.Formula="=vlookup(Range(""SomeName"").Offset(1,0),DataRange,2,False)"
In row E10:Z10 I have a series of values and in row E6:Z10 I have dates associated with each of the values in row 10.
I would like to create a formula to look in E10:Z10 for the maximum value and then return the date associated with that value. For example, if the maximum value is in P10, I would like the formula to return the date in P6.
I have tried without success to use the OFFSET function as follows:
=OFFSET(MAX(E10:Z10),-4,0)
I am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:
View 5 Replies View RelatedI would like to use the offset function as follows
Each Month a new column will be Inserted in Column V
I need to sum data starting three columns to the right of column V and then two column to the right of that plus BC15 (this will also change each time a column is inserted in Col V
I have used a sum formula before inserting a new column showing you what i am trying to achieve ...
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
