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
 Vlookup On A Cell That Reference Another File
 Vlookup, Using A Cell That Contains The Table Reference
 Return Cell Reference From MultiColumn 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
 Vlookupleft 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
 VLOOKUPlookup 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?
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
Return Cell Reference From MultiColumn 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
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 endusers. 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 (monthend 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
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
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 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 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
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 Range.Formula="=vlookup(Range(""SomeName"").Offset(1,0),DataRange,2,False)"
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 Mar09. 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 Apr09 column.
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 a2a16 i have week1week16. 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
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) and HTML ltb1.value = Application.VLookup(ComboBox1.Value, Sheets("Wk Data").Range("A3:A17"), 2, False) Will
View Replies!
View Related
Vlookupleft 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
Lookup Multiple Criteria & Sum Result On Multiple Criteria
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT((Sheet2!B2:Sheet2!B300="MARKETING"),(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300) 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.
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
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
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: VLOOKUP(x,LUTWK01,y,false) VLOOKUP(x,LUTWK02,y,false) VLOOKUP(x,LUTWK03,y,false) 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: ProjectA data1 data2 date1 date2 date3 ProjectB data1 data2 date1 date2 date3 ProjectC data1 data2 date1 date2 date3 ProjectD 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 "ProjectC", I need to know the max of the dates in the row for ProjectC; 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
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() Range("C7").Select ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))" 'category Range("c9").Select ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))" 'subcategory Range("c10").Select...................
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
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: Lookup The Title
I have a tab with the following on: Australia 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.
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#1ORIGIN INFO: Line#1 CONTACT NM Line#1 CONTACT PH# Line#1 CONTACT EML Line#1 SITE NAME Line#1 ADDRESS Line#1 CITY Line#1 ST ZIP CTY Line#1 DAY&WINDOW Line#1 LIVE/DROP? Line#1DESTIN INFO: Line#1 CONTACT NM Line#1 CONTACT PH# Line#1 CONTACT EML Line#1 SITE NAME Line#1 ADDRESS Line#1 CITY Line#1 ST ZIP CTY
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. EXAMPLE: 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
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
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 twoway lookup criteria.I have a chart for permissible weight as per height of a person.But there is another criteria of agee.g.col A (A8:A25)has the various heightgroups(from156cm to 190cm) and row 7 (B7:I7) has the agegroups(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
VLOOKUPlookup 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
