Vlookup Or Offset/match Formula
Apr 16, 2009
On the attached spreadsheet, i am trying lookup the selection name in column A on the summary sheet and search through column A of the other three worksheets adding together the three answers but something isn't working as the end answer is incorrect. I have tried vlookup and also tried combining offset/match but neither way i am getting the correct answer. I removed a lot of the data to simplify things and changed the figures to fictional ones for privacy reasons.
View 2 Replies
ADVERTISEMENT
Apr 24, 2014
How to use offset in combination with match and vlookup. Well I think I have to use Offset to find the value ( cell with time in it).
I have in my workbook 3 sheets: Sheet1, Sheet2 and Agents.
In 'Sheet2' every week I upload a report with persons and every person has a certain amount of time behind their name.
In 'Sheet1' I want to get (load) the data: the person and time from 'Sheet2'.
In 'Agents' I only match the names. That's because the names in the report I upload in 'Sheet2' have a different lay-out then the ones I use.
The matching and to get the names correct in 'Sheet1' Is no problem. Though I get stuck with the cells where the time is placed in the report I upload in 'Sheet2'.
The persons are in Column C ( C7, C26, C45, C64 etc) but the value I also need to get is not in line behind the names. It's In the 7th row under the name and in Column L.
Example:
Wiebe (C7) time ( L14)
Gary (C26 time ( L33)
Kay (C45) time ( L52)
What I use to match the names and get data is this formula.
=INDEX(Sheet2!$A:$L;MATCH(VLOOKUP(Sheet1!$A2;Agents!$A:$E;5;0);Sheet2!$C:$C;0);MATCH(B$1;Sheet2!$1:$1;0))
Is it possible to use Offset ( or something different) in this formula to also get the cells with the time ( matching with the right person)
View 14 Replies
View Related
Sep 22, 2009
I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large. In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code.
The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same. Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns H-AH. It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.
View 3 Replies
View Related
Jul 2, 2009
I am using an offset match formula to look at a named range of data and create a validation list of property address's based on certain criteria in cell d1.
My question is it appears the information which is status information seems to have to be sorted, otherwise the list doesnt work properly.
is there another formula to accomplish this with out having to sort the criteria in cell d1 ??
This is the formula i am curently using, it works great if "ListStatus" is sorted and not if unsorted.
=OFFSET(AddressDisplay,MATCH($D$2,ListStatus,0)-1,0,COUNTIF(ListStatus,$D$2))
View 9 Replies
View Related
Sep 22, 2006
I found the following formula, with different sheet and cell references, on someone's site: =OFFSET(CurvCalc!$E$12,MATCH(MAX(CurvCalc!$E$12:$E$10000)+1,CurvCalc!$E$12:$E$10000,1)-1,0)
I googled for the last couple of days but can't find this site again. I have found several that come close like Dave's at http://www.ozgrid.com/News/excel-dynamic-ranges-vba.htm
http://www.cpearson.com/excel/lookups.htm
http://www.cpearson.com/excel/lists.htm
and Microsoft's at
http://support.microsoft.com/default...;en-us;Q152407
View 3 Replies
View Related
Feb 16, 2014
A B C D E F
Latest Status
Latest Date
Current Status 1
Current Date 1
Current Status 2
Current Date 2
#VALUE!
Sun,16-Feb-14,11:0 PM
Take1
Sun,16-Feb- 14,10:41PM
Take2
Sun,16-Feb- 14,11:02 PM
My formula =IF(B3="","",OFFSET(A3,MATCH(B3,B3:F3,0),0,2)) in A2 is giving me #value error.
The correct solution should be Take2, while matching the condition from B2 latest date.
Sheet Attached. File Name-Latest.xls Zippyshare.com - Latest.xls
View 5 Replies
View Related
Dec 28, 2009
I have the following formula in a cell:
=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9
When the workbook with this formula is open it slows down other workbooks quite a bit.
If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.
How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.
View 9 Replies
View Related
Jul 16, 2014
I have a sheet that a VLOOKUP is looking at, and what I need is, if that VLOOKUP finds what it is looking for I need it to look at the cell 2 cells to the left, how is that done?
I am thinking something like:
Formula:
[Code] ......
I just don't know the offset part.
View 3 Replies
View Related
Jan 22, 2014
I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.
As a example i have attached the sheet.
View 1 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
Jan 11, 2014
I am trying to create a lookup formula where the cell value to match has a formula behind it.
For example: =INDEX(AR7:AR371,MATCH(G28,AE7:AE371,0))
The trouble I'm having is that the value in G28 is derived from a formula and the Index Match formula then gives a #N/A result. If I change the value in G28 (a date) manually, so directly enter a date, the Index Match formula works ok.
Is there any way of getting the Index Match formula to work, or Vlookup would also do although that at the moment that has the same problem with G28 having a formula behind it.
View 4 Replies
View Related
Oct 27, 2009
I have a worksheet that has two different years and I am trying to update a summary page that pulls the amount based on the year in the following formula.
View 5 Replies
View Related
Sep 21, 2012
HTML Code: VLOOKUP(M2,'FX Rates'!$A$1:$P$199,2,FALSE)
I have lots of vlookups in my spreadsheet. Match and Index is a better method and should speed up my spreadsheet (1000s of vlookups!).
View 5 Replies
View Related
Mar 26, 2014
I'm trying to create a rec report, ideally on one page, for multiple criteria. The situation is we get a report on our trade positions (investment stuff) from an outside service we use. We also have a report internally from what we've booked. We're looking to tie out are basic things like price, original par, revised par, trade date, counter-party banks, etc. I've consolidated data from both sources on one page (DATA) where all data points are lined up (say EXTERNAL is from rows 1-500, and INTERNAL is 510-1010). Now I need to create a front page showing the result of each tie-out. The lookup value will be a combination of the fund name + trade ticket number which would almost guarantee a unique ID (fund name XYZ, ticket number 12345 = XYZ12345)
Current tabs on spreadsheet: DATA, EXTERNAL, INTERNAL. I need to add a REC page
Question: What formula can I use to check on each and return TRUE/FALSE? The goal is to have about 8 columns on the REC, each for one of the tie-outs (let's use price as an example.). I know the basic VLOOKUP to return one value, but it appears in this case I will need VLOOKUP to check two matching values based on unique ID XZY12345, compare the price, and return TRUE if they match, FALSE if they don't
Here's something I saw from one of our spreadsheets that I think does the same to confirm counter-party, though I have no idea how to read this lengthy formula.
=EXACT(VLOOKUP(IF(Q2="COMPANY",R2,Q2),FUNDS!$M$1:$N$400,2,FALSE),
INDEX(PENDING!$G$4:$G$3218,MATCH(CP!C2,PENDING!$N$4:$N$3218,0)))
View 9 Replies
View Related
Mar 2, 2009
I'm trying to lookup a string in a column from another sheet and than return the row on which that string was found. I have two files, one in which my OFFSET/MATCH worked and another in which they didn't work properly.
My problem is this: I have multiple instances of a string in a column and i want to get all the info on that string (the rest of the columns). Let me explain in examples.
In column A on Sheet 2 i have
4 2 4
2 3 4
5 3 2
2 2 2
4 1 3
I need to have in Sheet 1:
4 2 4
4 1 3
Where the first 4 (bolded) is the reference value.
In sheet 1 i have a column A2 which is the reference. In column B2 i have
=MATCH(A2;'Sheet 2'!A1:A16051;1) - this worked on a file but returned a bad value on the other (donno why)
In the second file i used =MATCH(A2;'Sheet 2'!A2:A10552;0)+1 which returned the good value.
For the second value (row 4 1 3) i used =IF(OFFSET('Sheet 2'!A1;B2-2;0)=A2;OFFSET(Sheet 2'!A1;B2-2;0);0). This worked well on the first file but didn't work on the second.
I got the commands above from someone that used to update this file so i don't know exactly how they work
View 10 Replies
View Related
Oct 24, 2008
=IF(MATCH($A7,$BX$15:$BX$112,0)>150,"",OFFSET($BX$14,MATCH($A7,$BX$15:$BX$112,0),1))
If there is not match, I get #N/A in the cell. I would prefer to get a value of zero (or ""). I've been experimenting with ISNA and ISERROR.
View 4 Replies
View Related
Feb 7, 2014
I'm struggling with this.
Sheet2.A1 = Cat
On Sheet1, I want to find Cat in Column C and then Offset down 2 rows and over 57 columns
Put result on Sheet2, B2
View 3 Replies
View Related
Feb 2, 2009
I have in col J a series of values, these can be negative and positive numbers of any value. In col D I have the time (starting at midnight each day) that the values occured in col J. I am trying to look down col J until a value >= 50 is reached, I then want to look across at the corresponding cell in col D to find out what time this event occured. So, if a value of 50 or more is found in cell J232 for instance, I want to look across at cell D232 and extract the time found in that cell.
View 2 Replies
View Related
Apr 6, 2009
Can Offset be combined in an Index Match Match formula as per the attached sample?
View 4 Replies
View Related
Feb 17, 2009
I have one condition which needs to be matched. This code I wrote can find the first instance and then offset data to another worksheet accordingly.
My question is whether than can be done for every other instance that appears on my sourcesheet 'Invoicing'. I have considered putting a loop in, but this will only find the first instance over and over again which is pointless.
View 4 Replies
View Related
Jun 30, 2006
I'm having trying to set up a new
workbook to pull selected results from an existing one. I am trying to figure
out the formula/function for cells B2:D3 on workbook2:
EXISTING WORKBOOK1:
(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D is
dynamic range 'mtd')
A B C D
Joe P1 7
P2 1
P3 2
Total 10
Jill P1 3
P2 4
P3 1
Total 8
NEW WORKBOOK2
A B C D
P1 P2 P3
Joe 7 1 2
Jill 3 4 1
I entered the following as an array formula in cell B2 and it works fine to
return the proper result of 7:
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0))
This does not work to return the results for P2 or P3, I am assuming because
I need some sort of an offset for the different rows in workbook1.
I am hoping someone can point me in the right direction here. First I want
to locate the proper name in workbook 1 and then the proper product under
that name and finally pull the mtd col D result for that product and name.
View 10 Replies
View Related
Jul 10, 2008
I'm trying to create a formula that would return the value of the column next to it. Looking at the below example, in a new cell I would want to be able to return the value 7 based on the fact that it is in category b.
I would think an if/offset/match function would do this but I can't seem to figure it out as it may require nesting.
categoryvalue
a1
a2
a3
a4
a5
a6
b7
View 9 Replies
View Related
Oct 26, 2012
Any way I could SUM a YTD using VLOOKUP and OFFSET? In another worksheet, I need to reference Account Income #, go to the worksheet shown below, find the Account Income number, Offset and sum of the columns in that row to the YTD figure.
Attached is a sample : EXAMPLE.xlsx
Account
Income
9/30/2012
8/31/2012
7/31/2012
6/30/2012
5/31/2012
[Code] ......
EXAMPLE.xlsx
View 4 Replies
View Related
Feb 27, 2007
i have a table with customers and the rentals they have made. in the table it has their customer number and then the item code of the item they have rented out.
i want to have a table that brings up the item codes of what they have rented but with a vlookup it only brings back the first item they have rented and not the ones after that.
View 14 Replies
View Related
Jul 8, 2013
I have a data set that is laid out like this below:
Jan 12 Feb 12 Mar 12 Apr 12 May 12 Jun 12 Jul 12 Aug 12 Sep 12 Oct 12 Nov 12 Dec 12
Revenue $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000
On another sheet I want to find the Revenue and sum the Months Jan 12 to May 12.
So I would like to have my answer read $5000.
I am having a hard time doing the Vlookup and summing a range at the same time.
View 3 Replies
View Related
Apr 7, 2007
I need to add offset to this formula to return the value 1 row down. So it actually would return the values in I39
=VLOOKUP(A34,Correct!$A$2:$I$312,9,0)
View 9 Replies
View Related
Mar 11, 2014
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
View 2 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
Jun 3, 2014
=INDEX('Data Dump'!$C:$C,MATCH('YTD Detailed'!B$2&A40,'Data Dump'!$G:$G&'Data Dump'!$D:$D,0))
I have to above array formula, i am looking to nest an offset within it. That looks up two columns to the right, but im having trouble.
Can an offset be added to an index?
View 8 Replies
View Related
Aug 7, 2009
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)
View 2 Replies
View Related
