Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Vlookup:keep Cell Reference As Lookup Criteria

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Using A Vlookup And Returning A Cell Reference
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 Replies!   View Related
Vlookup On A Cell That Reference Another File
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 Replies!   View Related
Vlookup, Using A Cell That Contains The Table Reference
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 Replies!   View Related
VBA: Vlookup And Return Cell Reference
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 Replies!   View Related
Lookup Numbers In Sheet And Assign It's Cell Reference
I have a worksheet(Shed) and the range where numbers will be entered are B3:E46. In another sheet(Location) I have a in column A "Shift #", B "Shift Bus #" C "Location". what I am attempting to do is when a bus number is entered into Shed! ie.. b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and match it to Location! (column B) and from there provide the cell ref in column C. So I could then print Location! that would give me in Shift # order where each Bus # the shift is assigned to.

View Replies!   View Related
Return Cell Reference From Multi-Column Lookup
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 Replies!   View Related
V Lookup Or Hlook Up: Look Up The Cell Reference For Distance Between Two Customers
I have attached a spreadsheet with a small amount of the data I need to use. This is the distance between customers in kms. I need to be able to have excel look up the cell reference for distance between two customers though they will not always be the same customers every day in the same order.

View Replies!   View Related
Sumproduct By Reference Cell As Criteria
In reference to an earlier thread, which shg and ByTheCringe2 both were genereous in their assistance, I would like to add a wrinkle, as requested by the end-users.
how to reference an earlier thread for people to reference but you can search " Sumproduct By Last (criteria) Month With Data". Edit by ByTheCringe2: Sumproduct By Last (criteria) Month With Data.

We had used the MAX function to find the most recent investments, per counselor, per bank. I now need to find the most recent investments as of a certain date (month-end perhaps). I have ideas, but I'm afraid my misperceptions/assumptions might hinder me from seeing all available options. Initially, I thought I could use a text or listbox on the summary sheet to enter a date that would link to the reference cell on the data page, and this reference cell would then be used in whichever formula I came up with.

View Replies!   View Related
Vlookup Displays, If Reference Cell Is Blank?
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 Replies!   View Related
Reference A Cell For Tab Name In VLOOKUP Table Array
Sheet 1 contains:

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

Sheet 2 contains:

Item Data

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 Replies!   View Related
Add Vlookup, With Variables, To Cell To Reference Another Workbook Via Macro Code
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 Replies!   View Related
Prevent VLOOKUP Until Lookup Cell Has Data
On the uploaded workbook, Daily Nutrition Worksheet. How do I clear all the data to start fresh without deleting all the formula?

View Replies!   View Related
Vlookup = Lookup Returning Previous Row Cell Data
I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.

For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.

View Replies!   View Related
Two Page Lookup Matching Criteria Based On Cell Value
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.

With that said....
1. How do you put in the drop down box that references information on a different sheet?

2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?

View Replies!   View Related
Add VLOOKUP To Cell (Macro Code) & Offset From Range For Lookup Value
I'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


View Replies!   View Related
Fastest Lookup Method: Use A Key To Lookup A Value (VLookup, Index/Match, DGet, And The Rest)
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: Does Excel/VBA have a way to create a branch table for such lookups?

View Replies!   View Related
VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.

Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.

View Replies!   View Related
Lookup Maximum Value And Reference From There.
I need to look up the highest value in a column and then return a name in the same row as the value is found. I been playing with max and offset but they are not working for me.

View Replies!   View Related
Can Lookup Or Index Be Used To Reference To Different Cells
Can they be used to reference data based on data inserted into more than one cell? I have attached an example as its kind of confusing to explain.

View Replies!   View Related
Vlookup For Two Reference Points
i have a vlookup formula that i am using and i have two question

my first is something that i noticed while reading mrexcels book and comparing to what i was told here.
i was refered to use column()+0 instead of what mrexcel says of col(the number)-how many to get the number i want.

now my setup is like this

in a1 i have a sheet named anaheim batting totals in a2-a16 i have week1-week16. now i have my formula that i enter in b2 that uses the reference in a1 to look up in the range in sheet week1( i manually entered the range location.) i then copy paste it down to b17 with the dbl click trick and then manually go into each of those formulas and change week1 to week2 to week 3 etc. i want to be able to reference the A column title(whichever week appears) and the a1 cell in the same vlookup formula. in essence to say lookup a1 in this range ex.a1:l450 in the sheet that is in the corresponding a column,i.e. week1.16 all set the same with the same range. the rest i can figure out, but i would like to have one formula to copy to all cells of my chart.

View Replies!   View Related
Can Vlookup To Sum Same Reference Number
I am working on a data with a range of same customer number but different sales figure. If there any way I can search for a duplicate cusomer number and then summing up the sales? I tried to use vlookup but it only recognise the first reference number. I have attached the excel for your reference.

View Replies!   View Related
Combobox1 Vlookup Reference
I'm trying to use the Combobox1.Value as the look up value in my Vlookup formula. This is creating a type mismatch error at the moment.

I've tried both:

HTML ltb1.Text = Application.VLookup(ComboBox1.Value, Sheets("Wk Data").Range("A3:A17"), 2, False)

HTML ltb1.value = Application.VLookup(ComboBox1.Value, Sheets("Wk Data").Range("A3:A17"), 2, False)

View Replies!   View Related
Vlookup-left Reference
generally vlookup function gives a value from the right side cell. is it is possible to display a cell from the left side. in the attached excel file i want the numbers against the cell which contains mom.....

View Replies!   View Related
2007 - Invalid Reference - VLookup
Never got this message before with the old excel so i dont know how to resolve. I get this. "Invalid Reference. File conversion can not contain formulas that reference cells beyond a worksheet size of 256 columns(columns IW or higher) or 65536 rows." Im doing a VLookup and when i input the table array it is always invalid when i know it is valid. The spreadsheet im referencing does not have any formulas beyond that range either. Another guy i work with is now getting the same error with the new excel.

View Replies!   View Related
VLOOKUP - Indirect RangeName Reference
I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).

Cell A1 contains a fuel cost.

Cell A2 contains a formula that returns a RangeName.

In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP($A$1,$A$2,2,FALSE). The result is #NA!.

If I use =VLOOKUP($A$1,INDIRECT($A$2),2,FALSE), I get #REF!.

Among other things, I've also tried =VLOOKUP($A$1,TEXT($A$2,"@"),2,FALSE). No cigar.

View Replies!   View Related
Reference Different Concatenated Range Names In A LOOKUP Formula
I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:


LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:

VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.

When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?

View Replies!   View Related
Reference Range Cells From VLOOKUP Results
I have a workbook where one tab contains the data that I am given:

Project-A data1 data2 date1 date2 date3
Project-B data1 data2 date1 date2 date3
Project-C data1 data2 date1 date2 date3
Project-D data1 data2 date1 date2 date3

In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.

So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.

View Replies!   View Related
VLOOKUP - To Create A Cross Reference Tool
I'm using VLOOKUP to create a Cross Reference tool. CR is my "anchor" page that I'm attempting to tie the remaining worksheets WIT,TEC,COP back to the CR worksheet. I'm able to get VLOOKUP to work on the CR worksheet in columns H & I but unable to get the VLOOKUP to work in column J. The VLOOKUP function is entered but it does not return a value that I know exists in worksheet COP.

View Replies!   View Related
Countif Criteria With Named Reference
I'm trying to use a countif formula with a named reference but the formula is not calculated correctly. My example would be: countif(A1:A5,<max) with max being the named reference. I also tried various options using quotes around the named reference (ex. "<max") but that doesn't work either

View Replies!   View Related
Lookup Vs Vlookup
i am using Vlookup in all My files and the 4Th argument i put it 0 to give exazct result

My Question
Lookup Function give the Exact or approximate match and as i know Vlookup give the first result but Lookup give the last result

View Replies!   View Related
Identify Circular Reference (Changing Ranges, Vlookup(), If() And Sum())
Changing Ranges, Vlookup(), If() And Sum()

This is the link to the thread where ByTheCringe2 showed me the power of SUMPRODUCT and sorted my problem out. I don't think you'll need it, but I'd rather reference it before Roy reprimands me! lol

I am building on the same topic, though.

Hi All!

I have an investment where the monthly interest is added back to the capital. The next month earns interest on the higher figure and this higher interest is again added onto the capital and the story starts all over at the next month. OK, that's easy to do (Again, I think!).

All works fine UNTIL the end of the Tax year is reached and Tax on the interest earned is payable from the investment as the capital carried forward is reduced by the Tax payable. In the attached file I show how I calculate the interest earned for each following Tax year (which always ends at month 2) using SUMPRODUCT. Sumproduct is used because I never know whether the initial investment is made in month 5, 11, or any other month and by using certain controls (thanks ByTheCringe2!) I can simply total the interest earned in each tax year - the months between month 3 (start) and 2 (end).

On a seperate sheet I calculate the Income Tax payable as follows:

Firstly, I calculate the standard Tax payable on the investor's income from other sources. I then add the taxable portion of the interest to the standard income from other sources and calculate the tax payable on that combined figure. I then deduct the two figures from each other and get the increase in Tax payable because of the interest earned.

This "increase in Tax payable"-figure is then returned to the spreadsheet on which the interest is added onto the capital. See Sheet2! cells O9:O15. The Tax payable for each Tax year is placed next to taxable values I calculate using SUMPRODUCT (again!) in column N9:N15.

VLOOKUP is then used to transfer this Tax payable figure into the calculations (Column K9:K68) as and when required (only when tax is payable) - and should be deducted from the value in (Same row) Column H and that answer should be transferred to next row Column E (Balance at beginning of month) -

BUT it causes CIRCULAR REFERENCE faults and I am absolutely frustrated at not being able to figure out how to sort it out!

Sheet2, Column E9:E69 (See Sheet2!E19 for Comment explanation) in the attached workbook.

View Replies!   View Related
Auto Updating Range Reference In Vlookup Macro
I have the following macro which runs vlookups between two sheets in excel. Whenever i add columns to the range the vlookup column reference is not the correct cell. Is their any way I can adjust the macro so that the column number adjusts in the formula when a new column is added??

Sub template()
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))"
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))"

View Replies!   View Related
VLOOKUP() Style But 3 Value Lookup
I have a table that has, amongst many columns, a year, month and a name column.

I want to do a formula that takes in 3 values (name, year, month) and returns a sum of values found in a variety of other columnswhere these 3 values are matched.

I would know how to do this in SQL but wonder if Excel can do this type of thing on one large data table?

View Replies!   View Related
Two Lookup Values, Vlookup
I want to match two different values in the same row and then have it return another value in that row.

View Replies!   View Related
Vlookup Using Two Lookup Values
Is there a way to perform a vlookup with two lookup values?

I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.

View Replies!   View Related
VLOOKUP With The Lookup Value On A Different Sheet
Formula is on Sheet1 and table array is on Sheet1
but the lookup value is on Sheet2 in Cell B15

Below does not work, either does anything I have tried.


View Replies!   View Related
Vlookup: Lookup The Title
I have a tab with the following on:


I then have another tab which is similiar to this:

Australia Danny 23
Australia David 25
Canada Ben 35

Basically what I want is....where ive put some formula I want to lookup the title, in this case australia which is the cell above, search within the table then enter the name to the right of that. I have done this, however it only ever uses the first name on the list..... Example i always get danny on a role, i have managed then to get it to say david underneath but then if i copy the forumla down again it continues with danny, david etc.

View Replies!   View Related
Lookup Second Occurrence With Vlookup
I am using vlookup to find the ORIGIN data and DESTINATION data but the subheading is the same for both- see below(site name, city...). How do I find the second occurrence for the same heading?

Is there another command that would be better?

col A col B
Line#1 CITY

Line#1 CITY

View Replies!   View Related
Vlookup With Two Lookup Values
=VLOOKUP(C29,'Zero Days YTD'!B:Q,16,FALSE) is my current formula where C29 = Jack Bates.
As of this month Jack Bates is now in two places on worksheet Zero Days YTD. One is named "Jack Bates (from AMU 3/1/07)" and the other named "Jack Bates (to APU 3/1/07)"

I tried =VLOOKUP("Jack Bates (from AMU 3/1/07)"&"Jack Bates (to APU 3/1/07)",'Zero Days YTD'!B:Q,16,FALSE), but that didn't work.

View Replies!   View Related
Cell Reference :: Formula To Reference New Cells
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.


12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View Replies!   View Related
Absolute Reference (cell Reference Behind The Table)
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

View Replies!   View Related
Using Lookup(2,1/) For 3 Criteria
Currently using Lookup(2,1/...) to match 2 criteria before giving me my end result. It works perfectly. I'm now needing to match 3 criteria... is there an easy way to modify this to allow it? My Current formula looks like

=LOOKUP(2,1/(Upload!$D$1:$D$25&":"&Upload!$F$1:$F$25="Gordon Brown "&":Downing"),Upload!$I$1:$I$25)

And as well as matching Gordon Brown, Downing, I'd like to match the word Motor which it would search in Column E of the Upload sheet.

View Replies!   View Related
Lookup With 2 Criteria
I have attached a workbook with 2 worksheets Refund & Factor. In brief I wish to work out the refund on a ticket. To do this I have entered the start date & surrender date. In C29 I have a formula that gives the datediff in months & days. I then have some VBA that extracts the numbers and enters them into D29 & E29.

I now need a formula to look at the factor sheet and find the intersection between 3 (months) & 19 (days) which is 13.96 and copy this to B36 on the refund sheet.

View Replies!   View Related
Lookup With More Than One Criteria
I need some VBA help for my project...

attached is my sample spreadsheet...
I need the excel to be program so that when i input the 'Grade' and 'T', it will automatically select the py value.

Say i select the Grade S275, and input T= 10.4, thus excel will look up at the S275 table, then to the T< 16 (because T=10.4) and thus select '275' and input it at cell E7.

if i put input Grade S355 and input T=43, then it will look up at the S355 table, then to T<63, and then select '335' and inout at cell E7

View Replies!   View Related
Two Lookup Criteria
i am having problem with two-way lookup criteria.I have a chart for permissible weight as per height of a person.But there is another criteria of age-e.g.col A (A8:A25)has the various height-groups(from156cm to 190cm) and row 7 (B7:I7) has the age-groups(from 15 years to 48 years) .All figures of weight are within B8:I25.Now if i want to get the permissible weight of a person whose height and age are known ,how do i get using the lookup function?

View Replies!   View Related
Lookup With Two Criteria
I am having some difficulty creating a lookup that takes two values and uses them both to perform an action analagous to a vlookup. That is, use the two input criteria to select a roow of data, and then taking an input column number to return the resulting cell's value. I cannot concatonate and create an index because my second value is a number that may not match exactly. I also need it not to involve too many loops as it will be used thousands of times on the same sheet on my client's older machines.

I have seen the listmatch and matchinglists examples on Ozgrid and they do not quite address my need. I am not an excel novice, but my VBA is not that strong. the codes I have written to date ( attached) will not allow my data set to be on a different tab than the tab on which the function is used, as i require.

View Replies!   View Related
2 Criteria Lookup Without #N/A
I am trying to lookup the value in a table based on two criteria. For the purpose of illustration, I attached the file here. What I am trying to do is that I want to fill Table 2 with data from Table 1 which match the company and the dates. If all the dates match for the companies, that problem would be much easier, However, as you can see the dates from each company do not match on the same row,totally massed things up. In my real file I have 70 companies, any one have a solution for this??

View Replies!   View Related
Vlookup- Multiple Conditions For Lookup Value
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10

Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.

Is there someway to do this with a vlookup or maybe match index?

View Replies!   View Related
VLOOKUP-lookup Value Has Been Both TRIMmed And VALUEd
The formula I'm using is: =VLOOKUP($B724,Sheet1!$A$2:$S$17120,6,FALSE)

The B column in the lookup value has been both TRIMmed and VALUEd. The column that would contain the matches in Sheet1 has also been TRIMmed and VALUEd. I've confirmed that if I manually search for my value in Sheet1 then I find the data I'm looking for, but I always get #NA when this is run.

I have successfully done other VLOOKUPs between different columns in these worksheets, but can't get this one working. Excel version is 2003 SP2 if that makes a difference.

View Replies!   View Related
Vlookup Multiple Rows With The Same Lookup Value.
I'm trying to get a result of all rows in a table that has the same lookup value.

For example:


I want to look for David and that the result will be all rows that starts with David:

David 25
David 68
David 902

I tried using vlookup but it always returns the first row.

View Replies!   View Related
Copyright 2005-08, All rights reserved