Free Microsoft Excel 2013 Quick Reference

Vlookup NOT WORKING!

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


Post your answer or comment

comments powered by Disqus
I have a VLOOKUP formula that works when searching some cells but not others when both cases should work.

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.

Hey all,

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

Hi All

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

Hi I'm currently running office 2000 and trying to get vlookup to work
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

Hi,

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

Hey guys,

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!

Hi guys,

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?

Hi, all,

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)

Alright it's been a while since my last post, but I've come across another odd problem that I can't fix by myself.

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!

Hi I'm currently running office 2000 and trying to get vlookup to work
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

Hi all,

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

Hi,

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

My existing VLOOKUP function is not working ever since I copied the file. I am getting the following message - #N/A even though the lookup value exists.

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.

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

i have a down load from the web which was initially ".HTM " and opened in excel. my problem seems to be that i cannot get the two files i am using to act in the same way.

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

I have made several worksheets for lookup up pricing for our various product
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 iterate through each cell in "Column C" in downward direction.

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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi guys,
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!

I am doing a VLookup, but it does not work because the cell content I am looking up is somehow not the same as the cell content I am looking to pull from.

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.

Hi all, I'm playing with the VLOOKUP and can not get it to work correctly. Any help on where I'm going wrong would be appreciated!

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...

Hi

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?

Hi All

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

I have 2 files open. File 1 = Just supplier (there are many, File1A_month1,File1A_month2, File1B_month1,File1B_month2... File1C etc)
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?

I have a macro that is not working quite right, and would like some assistance with it. It doesn't always auto-fill all the way down. Any ideas? Is the ActiveSheet.Cells portion the possible culprit?

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)


I created a test for VLOOKUP that was working but when I tried to reproduce with real date, it doesn't work anymore. See the included table and formula.
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.