Hi there, my first time posting. I'm a basic user of vlookups, but I'm good at what I know about them. I cannot get this formula to work, however. Here's what I've tried:

1. Excel Options > Formulas > Workbook Calculation > Automatic.

2. Formatted my 'variable' (is that the right word to use?)columns to be the same, and I've tried text, general, and number. None work!

3. I've tried selecting one of the variable columns and clicked text-to-columns, then repeated that with the other variable column, no success.

4. I've tried copying and pasting both chunks of data into a clean and fresh spreadsheet, no luck.

I'm sprouting gray hair as I type this... and would be completely grateful for any help.

A million thank-you's for whoever can solve this for me! Spreadsheet is attached (if I did it correctly).

Thank you,

Jennifer

hot pumps_040711.xlsx

1. Excel Options > Formulas > Workbook Calculation > Automatic.

2. Formatted my 'variable' (is that the right word to use?)columns to be the same, and I've tried text, general, and number. None work!

3. I've tried selecting one of the variable columns and clicked text-to-columns, then repeated that with the other variable column, no success.

4. I've tried copying and pasting both chunks of data into a clean and fresh spreadsheet, no luck.

I'm sprouting gray hair as I type this... and would be completely grateful for any help.

A million thank-you's for whoever can solve this for me! Spreadsheet is attached (if I did it correctly).

Thank you,

Jennifer

hot pumps_040711.xlsx

- Does VLOOKUP not work if too many characters in cell?
- Vlookup not working
- Refresh External Data (VlookUps not working)
- Vlookup not working properly
- Cell reference in vlookup not working
- Listing a number when picking a name - VLOOKUP not working
- How on earth does my vlookup not work??
- DCOUNT, VLOOKUP not working for my project
- Vlookup not working, sumif does.
- Vlookup not working properly
- Vlookup not working in 2000 - worked in 97!
- Vlookup function not working if alphabet coming after '-' in the search term
- VLOOKUP not working...
- VLOOKUP not Working
- Vlookup not working
- VLOOKUP not returning results
- Application.VLookup loop not working.
- Vlookup function not working
- VLookup is not working
- VLOOKUP not working correctly
- Vlookup does not work & Wildcard available?
- User Form Returned Value Not Working in VLOOKUP
- Filepath not working with Vlookup
- Vlookup autofill macro not working quite right
- Newbie - VLOOKUP not working

Q: Does VLOOKUP have a certain maximum amount of characters it can search? If a cell contains more than this amount of characters will VLOOKUP not work?

This is what seems to be happening on my spreadsheet. The VLOOKUPS that refer to the cells with more than the average amount of characters seem to fail. The same happens with INDEX.

The Vlookup function is not working for a worksheet. Is it a condition that:

1. There should not be any columns between the range

2. Is it that the value returned should be a number or can it be a string also.

Please help on this.

Thanks

Praveen

Back again with a silly question I'm sure but ignorance is all I got.

What I'm trying to do is open several large external files, allow a lot of vlookups to work (hence the wait and the refreshAll's) and then close the reference files again.

My problem is the data is not refreshing .. at all .. not a sausage...

Problem is the refresh is not working. I know a data query would work better but this is disabled in my co and I appreciate the help of Excel Dennis in particular who tried to show me ADO possibilities (by the way Dennis I have Microsoft ActiveX Data Objects 2.8 Library)

The code (too long I know but I'm a beginer)

Sub Refresh()

Application.ScreenUpdating = True

Application.DisplayAlerts = False

ChDir "J:IFSSchroderscentrals"

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsAMORTISATION.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsCapStockDilution.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsDIVIDENDS.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsEXPENSES.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsINTEREST.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsTRANSACTIONS BONDS.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsTRANSACTIONS COACS.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:00:20")

Workbooks.Open FileName:= _

"LUXDA01DATAGROUPSIFSSchroderscentralsTRANSACTIONS EQUITIES.xls", ReadOnly:=True, Notify:=False

ActiveWorkbook.RefreshAll

Workbooks("TestDataUpdate.xls").Activate

Workbooks("TestDataUpdate.xls").RefreshAll

Application.Wait Now + TimeValue("00:01:01")

Range("I16").Select

Workbooks("TestDataUpdate.xls").Activate

Workbooks("AMORTISATION.xls").Close

Workbooks("CapStockDilution.xls").Close

Workbooks("DIVIDENDS.xls").Close

Workbooks("EXPENSES.xls").Close

Workbooks("INTEREST.xls").Close

Workbooks("TRANSACTIONS BONDS.xls").Close

Workbooks("TRANSACTIONS COACS.xls").Close

Workbooks("TRANSACTIONS EQUITIES.xls").Close

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

properly.

I have 10 tables which must be referenced from one formula.

The formula must pick which table to use (currently have that working)

the formula must pick which row and column to use (currently have that

working)

I know that that I just said the formula works and it does. However it only

works for numbers >= 76 and I need it to work for any numbers entered

Here is the exact formula being used:

=IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1 :U151"),Data!E3,TRUE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Da ta!E3,FALSE))

As I say this formula works just fine for me for numbers 76-150 (150 is the

highest number on the table) and returns the correct result. Numbers 1-75

however do not return anything and there is data in the table that should be

returned. (Note there are 151 rows in the tables on the other sheets)

The tables contain both numeric and alphanumeric data and where the formula

returns data both are returned fine.

One thing the formula does not do that I would like it to (though its not

neccessary) is in the event of no exact match returning the next largest

number that is smaller than the search value.

Hopefully this is a fixable problem.

If you need additonal info please just let me know.

Jaime

I am trying to use vlookup in Excel2003 to do some data manupulation

between data sets in two different sheets. My function looks like this:

=VLOOKUP(A62,Sheet2!A60:T11007,10,FALSE)

Cell A62 contains a number, for example: 3020200000

This function always returns #N/A.

If I enter the actual value instead of A62 then it works correctly.

I re-created the example in Excel's vlookup help, replaced the constant

value with a cell reference like I am using above and it was resolved

correctly.

What could be causing my application of vlookup not to work correctly?

Thanks for your help,

-Eric

I have a spreadsheet where I am trying to reference a lookup table in another sheet from a drop-down menu in my main page. For some reason the VLOOKUP function I am using is not working. I have tried many things:

Using [@ColumnName] or cell referenceUsing Names or specific cell rangesUsing "--" to try and reduce problems with text matchingUsing simple numbers as references to troubleshoot

I really can't understand why this isn't working, can someone help?

Thank you!

So there I thought that vlookups were pretty straight forward. I have used them and used them in conjunction with hlookups to get what I need. However when i am just about towards the end of completing what I need, a simple style v look up kicks me in the bottom and decides not to work.

I have attached two of the spreadsheets that work together to gather information. Column K of the vlookup spreadsheet in the master sheet should match the value in column I with the column in the spreadsheet Roster worksheet fruit mix column K and give the result in column L in the spreadsheet Roster worksheet fruit mix. How do I do that? So that chocolate matches chocolate in the table and gives me a phone number. Should be as simple as that but I cannot get it to work.

I thought that something did not work because i was referencing another spreadsheet but that is not the issue as I attempted the vlookup formula within the same master spreadsheet but that doesn't work too. Does anyone have any suggestions?

I have a list of about 300 unique names on sheet1 and a spreadsheet with about 17000 rows, representing one week of account activity, on sheet2. These rows contain multiple occurrences of each name, along with dates, times and minutes of use. On sheet3 I have broken down the range of dates and time frames I am trying to isolate into a number of criteria tables.

I need to determine the activity for each user within a designated time frame. I need a both a count of occurences and a sum of the minutes.

I've been trawling the net all morning and struggling away with DCOUNT, IF, VLOOKUP and a pivot tables, but so far have failed to find a solution. DCOUNT leaves the names out, VLOOKUP and pivot tables leave the time frame out, and IF doesn't seem to work at all.

Any suggestions?

Edited to add, this is the type of thing I am trying to do, which does not work:

=IF('sheet2'!A:A='sheet1'B5, DCOUNT('sheet2'!,12,'sheet3'!$B$3:$E$7), 0)

I have a spreadsheet that is trying to return data from a master sheet. Now it has 12 months listed in a column, with data for each month beside it. The child sheets draw data from the master sheet. By doing a simple vlookup on the dates. One month does not work. All months work aside from July 2007. The same cell DOES work when I type in a different month. The formatting has been changed between general, text and custom date. I have also copy/pasted the date between the cells to ensure they are identical.

The worst part is the same name I have some concatenates in another cell that reads from the date and those cells work fine, its just 2 cells causing the issue.

I made a sumif for the cell and it reads the data fine, but when I change it back into a vlookup it just reads 0% instead of the 9% that is there.

If there is anything else I can mention about this issue that will help fix it just let me know I'll put it up asap.

Thanks!

properly.

I have 10 tables which must be referenced from one formula.

The formula must pick which table to use (currently have that working)

the formula must pick which row and column to use (currently have that

working)

I know that that I just said the formula works and it does. However it only

works for numbers >= 76 and I need it to work for any numbers entered

Here is the exact formula being used:

=IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Data!E3,TRUE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Data!E3,FALSE))

As I say this formula works just fine for me for numbers 76-150 (150 is the

highest number on the table) and returns the correct result. Numbers 1-75

however do not return anything and there is data in the table that should be

returned. (Note there are 151 rows in the tables on the other sheets)

The tables contain both numeric and alphanumeric data and where the formula

returns data both are returned fine.

One thing the formula does not do that I would like it to (though its not

neccessary) is in the event of no exact match returning the next largest

number that is smaller than the search value.

Hopefully this is a fixable problem.

If you need additonal info please just let me know.

Jaime

I have a colleague who's comp had Excel 97. It now has Excel 2000 and

the vlookup formula is not working, despite having been created

originally in the older version. Even if one enters a new, valid

formula, it says that there is an error with the formula) Any ideas on

why this is happening?

Thanks

Rob

For some datas like,C-D1883,L-E2314 Vlookup is not working

I these case after the '-', a alphabet is coming.

In this cases 'VLookup' not working.

What will i do in these cases.

Please reply

Thanks,

Eler

Here is my formula: =VLOOKUP(B8,jancum,10,FALSE)

When I copy the data in B8 (numeric value) and perform a search for it within the jancum array - I can find the number. But I have to I pretend to edit B8 by invoking the F2 key & pressing enter. Not changing anything & then the #N/A is replaced by the appropriate value. I think I must be missing something simple....Please help.

I have tried everything that I know (including a work around that is way too time consuming) to get the vlookup to work. I have checked the formating of the cells (as someone suggested here) and still I am not seeing the expected results. I cannot identify the issue- I believe that it has to be in the formatting due to my work around working, but again my work around is too time consuming.

I have attached a sample of the file- and am hoping that someone can help. The data is there, it just will not pull it to the sheet, it keeps bringing back N/A.

If anyone can assist - I would be very grateful!

maryne

if colum "D" is a copy of colum "A" vlookup works, if i use colum "G" (this is the colum i wish to use [formatting can be changed]) , vlookup only works on full no's.

can any one suggest away around this

lines. These sheets look up a description, list price, and my cost in

another worksheet (in the same XLS file) based on the part number I enter on

the first sheet. The list on the second page has been assigned a name, and

I'm using VLOOKUP to find the part number in that range, and then return the

values into my original sheet. The list is sorted alphabetically by the part

number. I can send the file is someone can help!

My problem is that if the lookup list has over about 5,000 items, the

results of the VLOOKUP are intermittent. I can actually copy and paste a

value from the list into my sheet and it won't look up. Some of my price

lists are over 15,000 items.

Does anyone have any experience with VLOOKUP not working in a large list?

How about any ideas of how to make it work more reliably. I have one sheet

I'm trying to make that I expect to have about 50,000 items in it. I

desperately need to get this worksheet working. Please help!

I've to fetch values from the corresponding cells for which I'm using vLookup. I need to fetch multiple values so I would be using more vLookups in the loop after I get this working.

All these fetched values would be clubbed and based on conditions counters ,like one of them being "A" would be updated and summed at last to find the sum of each counter variables.

Here I'm trying to do is use loop for application "VLookup", but for some reasons it is not working.

I've tried using

..VLookup( Range("C" & i), ...

..VLookup("C" & i, ...

..VLookup(Cells(i, "C"), ...

i is the counter and C is the column. Could you please help me with it? I've been searching for help from past 8 hours. Finally I had to paste. I'll attach the file too.

VB:CalculateApril() Dim i As Integer, A A = 0 Dim isDecomm Dim myRange As Variant, mySelectedArea mySelectedArea = ThisWorkbook.Worksheets("MSL").Range("C2:G22") For i = 2 To LastCellInColumn - 1 With ActiveSheet ' Define Range myRange = "C" & i ' Issued in April. Checks for the Month If Range(myRange).Value = 4 Then A = A + 1 ' Are they Decommissioned ? Yes = Ignore, No - Count isDecomm = Application.VLookup(Cells(i, "C").Value, Range("C2:G22"), 5, False) Debug.Print Cells(i, "C").Value 'MsgBox isDecomm End If End With ' Increment i/Row Next i End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I'm at a loss to understnad why the vlookup function in the attached spreadsheet is not working. I have copied it from another spreadsheet where it is working fine but I can't ascertain what is different. It is supposed to put the week number in J1 based on what is selected in G1 and matched against the entry in the Dates sheet.

Could anyone offer any assistance?

Thanks!

When I copy the info. I am looking up from the source to the target, the lookup works.

Has anyone else ever had this problem?

Thank you for the help.

I have two worksheets: one is named "Table" that contains in column A many values to be matched. Then in column B are values that I want transferred over when there is a match.

The other worksheet is named "unit2" and in column F are the same values as in column A under "Table" for matching purposes.

At the moment, they are both sorted the same, ascending, ...and this is the formula I'm using at the moment (Which I insert in column N in the "unit2" worksheet. This is where I want the value from column B in "Table" to appear. :

= VLOOKUP(F2,table!A:A,1)

Now this only returns what is in Column A under "Table" I think, either way it's just the value from which I am using to MATCH. It's not the value I want to return....

= VLOOKUP(F2,table!A:A,2) will return a #REF! error...

= VLOOKUP(F1,Table!A:A,1,FALSE) will return the same value as using = VLOOKUP(F2,table!A:A,1) .

Obviously, I have something wrong...... I've tripled checked to make sure my Matching column's data were the same, and they are! What else can I do? Thanks...

Please have a look at the spreadsheet and see if anyone can advise why Vlookup or Advance filter does not work - #N/A althought the EXACT data is TYPED in.

Can Vlookup accept wildcards?

I'm using a UserForm to prompt the user for a fund number in the input box.

This is working fine and it is placing the fund number in the correct cell. There is a bunch of hiding and unhiding going on here also but it is not that significant.

What is important is that I then want to use the returned fund number in a lot of Vlookups to a sort of excel database within the workbook. The problem is that the lookup does not work with the value returned , however if I manually type the same number into the same location as the user form returns the data refreshes without a problem.

Any suggestions , I'm all ears

DennisJ

p.s. code for user form below

Sub InputFund_Click()

Dim Lastline As Variant

Lastline = Range("B70").End(xlDown).Address

Range(Lastline).Activate

Dim Target As Variant

Range(Lastline).Offset(1, 0).Activate

ActiveCell.Value = AddFund.FundNumber

Range(Lastline).Offset(1, 1).Activate

ActiveCell.Value = AddFund.FundName

Columns("A:CP").Select

Selection.EntireColumn.Hidden = False

Range("D17:BZ17").Select

Selection.Copy

Range(Lastline).Offset(1, 2).Activate

ActiveSheet.Paste

Application.CutCopyMode = False

Columns("A:A").Select

Selection.EntireColumn.Hidden = True

Columns("C:G").Select

Selection.EntireColumn.Hidden = True

Columns("I:J").Select

Selection.EntireColumn.Hidden = True

Columns("M:Q").Select

Selection.EntireColumn.Hidden = True

Columns("R:R").Select

Selection.EntireColumn.Hidden = True

Columns("U:V").Select

Selection.EntireColumn.Hidden = True

Columns("Y:Z").Select

Selection.EntireColumn.Hidden = True

Columns("AG:AG").Select

Range("AG8").Activate

Selection.EntireColumn.Hidden = True

Columns("AI:AL").Select

Range("AI8").Activate

Selection.EntireColumn.Hidden = True

Columns("AO:CB").Select

Range("AO8").Activate

Selection.EntireColumn.Hidden = True

Range("B94").Select

MsgBox "Thanks. You have added a new fund. Please test briefly to ensure the report function is not effected."

AddFund.Hide

End Sub

File 2 = All suppliers (each supplier on separate tab with month)

I will have to do multiple instances of this for multiple months. When both File 1 & File 2 are open- no problem. When File 1 is closed and File 2 is trying to reference it, I have an issue.File 2 has the Vlookup formula in it. It is the file with each supplier as a separate tab.

I'm using Indirect because I have File 2 with each supplier in a separate tab and I have to reference each to its parent file and month. So if I have tab SupplierA_July, I want a formula that looks up the item # and goes to correct File (matching company & month) and pulls back data in column 8. I then want to copy this formula for SupplierB_july, SupplierC_july etc.....then want to do the same for Aug, and other months. I would have to do so many separate Vlookup formulas for each tab. I thought using indirect, I could use one formula for everything:

Here is my formula:

=VLOOKUP($B9,INDIRECT("["&$C$1&".xls]"&$B$1&"!$B$4:$J$47"),8,"FALSE")

B9= Item #

C1 = tab name (from other file)

B1= File name (from other file)

That translates to:

=Vlookup(Item_number,[File1Name.xls]File1_tabname'!$B$4:$i$47,8,false)

(This one Works)- it looks at Item# in file 2(B9), finds Item# in File 1 and gives me the 8th value from Column B:

This does not work:

VLOOKUP($B9,INDIRECT("'"&"C:CUSTOMERSClientFolderCategoriesCategoryIndividual Suppliers"&"["&$C$1&".xls]"&$B$1&"!$B$4:$J$47"),8,"FALSE")

I have over 50 files similar to File 1, they are each an individual supplier. File 2 has all items all suppliers, each supplier in a separate tab. I have a formula in file 2 on each tab that looks up the tab name and month and I want it to vlookup from the File with that tab name. So file 2 may have tab1=Client1_Jan and tab2=Client2_Feb... So I can copy the same formula from tab to tab I want to say vlookup(item#, from client&month file, 8,false)

Any ideas?

Code:

Dim rng As Range Dim LR As Long LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False Sheets("Audit New").Select Range("C1").Select ActiveCell.FormulaR1C1 = "Baseline Old" Range("D1").Select ActiveCell.FormulaR1C1 = "Changed?" Range("C2").Select Range("C2").Formula = "=VLOOKUP(RC[-2],'Baseline Old'!C[-2]:C[-1],2,0)" Range("C2").AutoFill Destination:=Range("C2:C" & LR) Range("D2").Select Range("D2").Formula = "=IF(ISNA(RC[-1]),""new"",IF(RC[-2]=RC[-1],""no"",""YES""))" Range("D2").AutoFill Destination:=Range("D2:D" & LR)

The only difference is that this data was exported from a Access table.

Can somebody help a newbie on this please? Thanks !!!

******** ******************** ************************************************************************>Microsoft Excel - test-persl.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD1D2D3D4D5D6D7D8D9D10=

ABCDE112042*22942286222401*22942411332402*22942407452222*22942399562224*22942408672248*22942409782249*22942403892251*229424049102290*2294241010112294*22942405tblPersonnel*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

No luck finding an answer? You could always try Google.