VBA: Vlookup And Return Cell Reference

Nov 11, 2008

i am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).

I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:

View 3 Replies


ADVERTISEMENT

Reverse Vlookup (get Value From Reference One Column And Return Value By Corresponding Row From Another Column)

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

How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

View 3 Replies View Related

Using A Vlookup And Returning A Cell Reference

Nov 14, 2008

I have a sheet which uses a vlookup to find the data on a large sheet. normally to get the cell reference of the data i would use cell("address",......

However this appears not to work with a vlookup.

View 10 Replies View Related

Vlookup, Using A Cell That Contains The Table Reference

Dec 12, 2008

I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.

However I am getting the #VALUE error when trying this.

Simplified Example:
I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE)
I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.

And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE)
This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.

View 2 Replies View Related

Finding Cell Reference Using VLookup

Nov 15, 2013

I am currently having real problem using the formula below:

=INDEX(Data!C:C,MATCH(B7,Data!A:A))

What i am trying to do with this formula is return the cell address of an item that is in a different sheet.

The reference the for search is in Cell B7 which is a date, it then looks at a different sheet "Data" for this date which is in Column A. It then returns the Cell Reference of Column C of the same row.

Currently it is just returning the value of column C and not the cell reference.

View 3 Replies View Related

Vlookup On A Cell That Reference Another File

Jan 9, 2009

I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?

View 9 Replies View Related

Cell Reference Return Blank Or Zero?

Jun 15, 2013

In sheet2 I've many numeric value.

In sheet1, A1: =Sheet2!B12 which returns 0 (zero) though B12 of sheet2 is the result of =sum(B1:B11) i.e 660. But if in sheet1 A1=Sheet2!D12 or any other cell in sheet2 instead of B12, A1 gives correct result. That means cell A1 itself is not a problem. I checked format of B12 which is number format.

View 5 Replies View Related

Match & Return Cell Reference

Jun 16, 2006

I have a column of data in column and a column of data in column F. The data in column F is exactly the same as column C but in a different order. Is there a way to match column C to Column F and return the matching cell references for column C & F in column G & H? i.e. If the word Help appears in C27 and F40, match the two words and return C27 in G27 AND F40 IN H27. All of the values are unique (I hope).

View 2 Replies View Related

Reference Cell & Only Return X Characters

Oct 20, 2006

how I would go about referencing a cell on another worksheet to return only the last so many characters from the data in that cell. EG:

Sheet 1 cell A3 contains the number (stored as text) 12345678
Sheet 1 cell A4 contains the number (stored as text) 98765432

On Sheet 2 I want to put into cell B2 5678 (the last four characters from the data in Sheet 1 A3.) Also on Sheet 2 I want to put into cell B3 5432.

View 2 Replies View Related

VLOOKUP Where Reference MATCH Cell Has A Formula Behind It

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

Vlookup Displays, If Reference Cell Is Blank?

Jan 22, 2009

I am trying to use vlookup so that when each site name is selected the relevant comments for that site are displayed.

I have a formula that is working, but it displays "0" if there is nothing in the cell. How do i get it to display a blank cell if thre is nothing in the reference cell.

this is the formula that i am using:

=IF($C$4="", "", IF($C$4="No Match", "", (VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0))))

The site name appears in C4, and is selected from another sheet in the workbook.

View 5 Replies View Related

Vlookup:keep Cell Reference As Lookup Criteria

Jan 7, 2010

See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.

View 2 Replies View Related

Reference A Cell For Tab Name In VLOOKUP Table Array

May 27, 2009

Sheet 1 contains:

Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)

Sheet 2 contains:

Item Data
ABC 2

I'm trying to get the vlookup to return the value "2"

Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.

The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?

I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.

View 3 Replies View Related

Using VLookup (But Reference A Cell As Column Index)

Aug 10, 2012

How can I do the following....if for example I have the following vlookup;

Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false

How can I change the column index i.e. the 3, to reference to a cell.

Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false

The above example doesn't work but I'm sure something can be done using TEXT or VALUE

View 3 Replies View Related

Get Cell Reference As VLookup Result Of Different Sheet?

Dec 12, 2012

In workbook 1, I have summary sheet with columns

A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate

Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .

View 5 Replies View Related

Return Cell Reference Of Large Formula

Jul 23, 2008

I am creating a summary financial sheet in Excel 2003, Win XP. It needs to show the top 10 sales variances from a large data list, along with the associated department.

I can bring in the top 10 largest variances using Large(array,k), but because variances are either positive or negative, I'm not really showing the true top 10. To fix this I can use absolute values via Large(abs(array),k) but then I don't know how to convert the value back to its original sign (positive or negative).

If I could somehow get the cell reference that the Large(abs(array),k) formula points to, I could do something like this:

=Large(abs(array),k) * (Large Cell Reference/Large Cell Reference)

I'm afraid to use the address function because of duplicate sales variances. I often have several variances of with same value, so I might not truly be pulling the address that the large(abs(array),k) is pointing to.

View 9 Replies View Related

How To Reference A Cell In Another Workbook And Return Cell Value And Comment

Nov 2, 2012

What is the best way to reference a cell in another workbook and return the cell contents and the comment on that cell. I would like the comment to come across as a comment in the new workbook becuase the comment is actually a picture.

Hope this makes sense. I did find a macro through googling but I couldn't get it to work? I don't really want to copy and paste because eventually I have hundreds of sheets & thousands of cells to refer to.?

View 2 Replies View Related

Using Formula With Cell Reference To Work In VLOOKUP Function

Jan 30, 2014

I have problem with a spreadsheet that I am trying to create.

I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.

Now for planning purposes, I would like to see how much of each item I have on order.

I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:

=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.

For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is

A108: ="A"&MATCH(D11,'purchase order'!A:A,0).

Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.

View 6 Replies View Related

VLookup (or HLookup) Cell With Reference Rather Than Actual Number

Oct 13, 2011

I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).

So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.

A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #

However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.

When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.

Can I do a vlookup and keep my cell references?

View 6 Replies View Related

Return Cell Reference From Multi-Column Lookup

Dec 13, 2006

i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)

it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!

i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).

any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!

should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?

View 8 Replies View Related

Add Vlookup, With Variables, To Cell To Reference Another Workbook Via Macro Code

May 21, 2008

I am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1

The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.

View 9 Replies View Related

Return Cell Above VLookup

Nov 17, 2012

I am trying to use vlookup to locate a cell in another work book. The identifier is in the same workbook as the vlookup formula however the table and column to look within is in another. The identifier will also show up in the other work book where the table resides.

View 4 Replies View Related

Return Cell Value 30 Cells After Reference Cell

Apr 1, 2014

I want to find the cells 30 past the reference cell and the corresponding value:

i.e. =Sheet1!D312 to =Sheet1!D342 (=Sheet1!D(312+30))

or

=Sheet1!D312 to =Sheet1!E312

is there a way to automate this without having to manually edit each formula?

View 8 Replies View Related

Cell Reference Or Adjacent Cell Return

Apr 5, 2008

I have a formula that searches through a couple columns, and based on some criteria on those columns, it returns a value. The criteria looks at the dates in a given week, finds the earliest date, and then the largest value for all entries of the same date. So, I do not know what this vale is going to be in advance. In any case, Let's call this value1. This is all working fine!

Now, what I need to do is grab the value in an adjacent column to the left(same row) and add that to value1. Let's call this adjacent column value, value2. I was thinking that I could use the OFFSET function in this way, OFFSET(cell reference of value1, 0, -1) to return value2.

But this requires that I can get a cell reference for Value1. I cannot find out how to get a cell reference. I saw some VBA code to search and return cell references, but it assumed that you know what value you are looking for, and I do not know that in advance.

View 9 Replies View Related

Vlookup To Return Blank Cell Instead Of #N/A

Aug 15, 2009

is there anyway to have a vlookup formula display a blank cell instead of #N/A if the value it was looking for is not found. For example the first spreadsheet contains the persons name say Bob, the 2nd spreadsheet that the vlookup formula is searching does not contain the name Bob right now excel shows #N/A but what I want it to show is a blank cell. I've tried conditional formating to just white out the text if #N/A appears but can't seem to get that to work.

View 7 Replies View Related

Have VLookup Return Blank Cell Rather Than N/A Or 0?

Aug 3, 2012

I have a Vlookup, it returns a number, and when it doesn't find a match it returns N/A, and when it does return a match and the return value cell is blank, it returns 0. Is there any way I can have this so when it doesn't find a match, it returns a blank cell, and when it finds a match and there is no value in the return cell, it returns a blank cell as well, rather than 0?

View 6 Replies View Related

Return Cell Below VLOOKUP Result

May 30, 2006

After using a vlookup to find a value in a cell I want it to return the cell belows information.

View 2 Replies View Related

Vlookup 2nd Cell To Return The Data

Jan 16, 2007

I am struggling with the Vlookup function on Excell 2003. I am trying to lookup data with refernce to two cells to return the data. for example cell A1 = 1 AND cell B1 = 2 then return column 3 data. However I need this in a lookup or something very similar.

View 8 Replies View Related

VLOOKUP Returns N/A - Return Blank Cell

Apr 28, 2009

I have a normal VLOOKUP of this loookup range:

A 1
B 2
C 3
D 4

=VLOOKUP(T14,U6:V9,2,TRUE)

If the value in T14 is in the lookup range, I get the expected value returned. If the value exceeds the vlaues in the lookup range (e.g. I enter "E"), I get the last value in the range (4). How do I get it to return a blank cell if the entry in T14 is not found?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved