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
Related Forum Messages:
- Using A Vlookup And Returning A Cell Reference
- Vlookup On A Cell That Reference Another File
- Vlookup, Using A Cell That Contains The Table Reference
- Return Cell Reference From Multi-Column Lookup
- V Lookup Or Hlook Up: Look Up The Cell Reference For Distance Between Two Customers
- Sumproduct By Reference Cell As Criteria
- Vlookup Displays, If Reference Cell Is Blank?
- VBA: Vlookup And Return Cell Reference
- Lookup Numbers In Sheet And Assign It's Cell Reference
- Reference A Cell For Tab Name In VLOOKUP Table Array
- Add Vlookup, With Variables, To Cell To Reference Another Workbook Via Macro Code
- Prevent VLOOKUP Until Lookup Cell Has Data
- Two Page Lookup Matching Criteria Based On Cell Value
- Vlookup = Lookup Returning Previous Row Cell Data
- Fastest Lookup Method: Use A Key To Lookup A Value (VLookup, Index/Match, DGet, And The Rest)
- Add VLOOKUP To Cell (Macro Code) & Offset From Range For Lookup Value
- VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1
- Lookup Maximum Value And Reference From There.
- Can Lookup Or Index Be Used To Reference To Different Cells
- Vlookup For Two Reference Points
- Combobox1 Vlookup Reference
- Vlookup-left Reference
- Lookup Multiple Criteria & Sum Result On Multiple Criteria
- 2007 - Invalid Reference - VLookup
- VLOOKUP - Indirect RangeName Reference
- Can Vlookup To Sum Same Reference Number
- Reference Different Concatenated Range Names In A LOOKUP Formula
- Reference Range Cells From VLOOKUP Results
- VLOOKUP - To Create A Cross Reference Tool
- Countif Criteria With Named Reference
- Lookup Vs Vlookup
- Auto Updating Range Reference In Vlookup Macro
- VLOOKUP() Style But 3 Value Lookup
- Two Lookup Values, Vlookup
- Vlookup Using Two Lookup Values
- VLOOKUP With The Lookup Value On A Different Sheet
- Vlookup: Lookup The Title
- Lookup Second Occurrence With Vlookup
- Vlookup With Two Lookup Values
- Cell Reference :: Formula To Reference New Cells
- Absolute Reference (cell Reference Behind The Table)
- Identify Circular Reference (Changing Ranges, Vlookup(), If() And Sum())
- Using Lookup(2,1/) For 3 Criteria
- Lookup With 2 Criteria
- Lookup With More Than One Criteria
- Two Lookup Criteria
- Lookup With Two Criteria
- 2 Criteria Lookup Without #N/A
- Vlookup- Multiple Conditions For Lookup Value
- VLOOKUP-lookup Value Has Been Both TRIMmed And VALUEd
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?
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.
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.
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?
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.
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.
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:
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.
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:
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.
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.
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?
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
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.
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.
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)
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.....
Lookup Multiple Criteria & Sum Result On Multiple Criteria
I am trying to solve a problem. I am currently using this formula
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
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.
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.
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.
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?
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.
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.
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
Lookup Vs Vlookup
i am using Vlookup in all My files and the 4Th argument i put it 0 to give exazct result
Lookup Function give the Exact or approximate match and as i know Vlookup give the first result but Lookup give the last result
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??
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))"
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?
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.
Vlookup: Lookup The Title
I have a tab with the following on:
SOME VLOOKUP FORMULA
SOME VLOOKUP FORMULA
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.
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 CONTACT NM
Line#1 CONTACT PH#
Line#1 CONTACT EML
Line#1 SITE NAME
Line#1 ST ZIP CTY
Line#1 CONTACT NM
Line#1 CONTACT PH#
Line#1 CONTACT EML
Line#1 SITE NAME
Line#1 ST ZIP CTY
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.
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.
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.
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.
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.
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.
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
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?
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.
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??
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.