Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

why doesn't vlookup function work?

I have been using the Vlookup function, it works well in certain aspects but not all. Sometimes it does not work but returns incorrect data. Recently I am trying to have my works computerized. I have four figures (for example 1234 represent wood material and 5678 for metal) and another four figures (1100 for painting and 1300 for cutting) represent work done. If I have an item of painting on wood material , I would quote 12341100. My problem is an incorrect description and price would appear when I use such lengthy code to vlookup! If I only name the items with 1 to 10000 then the trouble disappears. Would someone please help me?


Post your answer or comment

comments powered by Disqus
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!

Guys, why doesnt the below code working, i am simply asking the macro to delete entries which are less than 5 days old, but it doesnt do it, i dont get any errors either

Code:
 
  With Range("A1:J1")
        .AutoFilter Field:=6, Criteria1:="

How does the Vlookup function work in terms of the source spreadsheet?
Am I right in thinking that the source spreadsheet is "attached" to the target spreadsheet in some way? Hence the large file size of the target one?

Is there anyway round this?

Thanks

Hi guys,

I have been trying to get vlookup function working for this workbook attached in this post.
I have a worksheet(A) with ID & Number columns.
I would like to lookup the matching ID in worksheet (B) and have the number in worksheet (B) printed into the Number column in worksheet (A)

here's the formula that i am using
=VLOOKUP(B2,B!A2:B8,1,0)

I have been trying to figure out what's gone wrong, and have followed several online tutorials, yet I am still getting #NA under the Number column in worksheet (A)

Any help would be much appreciated as I have been working on this for the entire day! xD

Cheers!

Hi, I want to do an easy thing, I have an inventory program with 10 codes in
one column(A3:A13) and 10 descriptions in the other (B3:B13), I want that the
user type a code in cell A1 and then in cell B1 appears the description, and
when the user type the description in cell B1 appears the code in A1, I have
the following proceedure :
Private Sub Worksheet_Change(ByVal Target As Range)
on error goto ErrorHandler
application.enableevents = false 'No events will fire
if target.address="$A$1" then
cells(1,2)= "=vlookup(A1;A3:B13;2;false)"
end if

if target.address="$B$1" then
cells(1,1)= "=vlookup(B1;A3:B13;1;false)"
end if

ErrorHandler:
application.enableevents = true 'Reset the events.
End Sub

It's not working, it's driving me crazy, I dont understand why??????

If I put the "=vlookup(B1;A3:B13;1;false)" without the "=" (like this
"vlookup(B1;A3:B13;1;false)") then appears in the cell this:
vlookup(B1;A3:B13;1;false)
so the Vlookup function is not evaluated.

Please HHHHHHHHEEEEEEEEELLLLLLLLLLLLLPPPPPPPPPPP

TIA

Hi Friends,

I am attaching a file where I have used the following VlOOKUP function. My question is, if Vloolup finds there is no data, then I want the cell to remain blank, but I find that there is 0 in that cell. Why is this happening to this file? I have used this function before in another file and it worked OK.

=IF(COUNTIF($A$3:$A$9000,$E3),VLOOKUP($E3,$A$3:$D$9000,1,FALSE)," ")

Thank you for all help

Angela

I have 2 sets of data on 2 different worksheets (but in the same work book) that I want to compare. I want Excel to look up a number from worksheet 1, find that number on worksheet 2, and bring back the data in the 9th column (but same row as the number in worksheet 2). So I tried the V Lookup function, but I can't get it to work. The formula I entered was:

=VLOOKUP (E16,Sheet3!$A$5:$P$136,9,FALSE)

E16 is the cell on page 1 that the number was in that I wanted to lookup on page 2, Sheet 3 was the page I wanted it to look in, in the range of A5 to P136, and 9 is the column that I wanted it to take the data from.

I am not understanding why it is not working. I have played around with it quite a bit and the only thing I can think of is that the data from cell E16 that I am trying to find on Sheet 3 is linked from another page. Is it possible that that is the reason? And, if so, how can I get this to work?

THANKS!!!

I'm using Office 2000 Professional and I have a vlookup function that works
in most cases, but not for some. I have a formula for a discount factor and
a formula that rounds that factor to the nearest 0.5%. I then look up that
result in a table but there are six values that it does not find (-17.5%,
-20.5%, -23.5%, -28.5%, -34.5% and -35.0%) Any ideas why it cannot find
these certain values in the lookup range and is there any way to fix this?

To see the problem, put the following info & formulas into a blank worksheet:
Cell A1 - "11111"
Cell A2 - "-500"
Cell A3 - "-450"
Cell B2 - "=A2/A$1"
Cell C3 - "=IF(B25%,18.5%,IF(C2

Hello,

For some unknown reason (to me at least), when I try to use the VLOOKUP
function now (on the same spreadsheet that I've been using for months), it
only shows the formula, and not the value that I'm trying to obtain.
However, when constructing the VLOOKUP function, when I fill in the
Lookup_Value, Table_Array, Col_index_num, and Range_Lookup fields, each time
I step through the fields, the values on the right of each respective field
ARE correctly shown. And down in the left corner, where it says "Formula
Result", that value is correct. But when I hit "OK", the only thing that
shows is the formula. Also, when I go the cell with the VLOOKUP function in
it, and right-click, and look in the "Sample" window, the value IS ALSO
correctly shown.

Cells that are using the VLOOKUP are formatted to General format.

Somehow, somewhere, I have changed a setting or something.

Why won't this work?

Thanks in advance for your replies.

Phil.
BTW, when I try it on a brand new file, it works fine.

hi to all!!

Im having problems with the vlookup function, I have two big ddbb of about 30x1500 each and im trying to add data from one of them to the other using the vlookup fuction. the common field for both DDBB is the id number. this numbers match one to one in both DDBB (i have checked that by doing a pivot table) but when it comes to comparing both ID numbers on Datbases. Many Id numers are not recongnized by the vlookup function giving me an N/A like the number wasnt there.i check one ID number in both ddbb and they are the same. I dont know what is the cause for that .All the fields are numbers.
I hope any of you have an idea of what is happening, the issue looks that easy that i dont know what ican try
I have attatched a little example of both data bases and the vlookup function not working
thanks in advance to you all!
Carlos

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,

I used match function but it come out N/A# when the lookup value is not selected in the same lookup array range. Eg

match(A1,G2:G4,0). A1 text is in the range of G2:G4, however the result come out as N/A#.

But if i use (G3,G2:G4,0) and it works.

The spelling is correct ( i copy and paste the text from G3 to A1).

Do you know any reasons why it was not working?

Thanks

I'm using Office 2000 Professional and I have a vlookup function that works
in most cases, but not for some. I have a formula for a discount factor and
a formula that rounds that factor to the nearest 0.5%. I then look up that
result in a table but there are six values that it does not find (-17.5%,
-20.5%, -23.5%, -28.5%, -34.5% and -35.0%) Any ideas why it cannot find
these certain values in the lookup range and is there any way to fix this?

To see the problem, put the following info & formulas into a blank worksheet:
Cell A1 - "11111"
Cell A2 - "-500"
Cell A3 - "-450"
Cell B2 - "=A2/A$1"
Cell C3 - "=IF(B2<0,MROUND(ABS(B2),0.005),-MROUND(B2,0.005))"
Cell D2 - "=IF(C2>5%,18.5%,IF(C2<-40%,0, VLOOKUP(C2,I$2:J$92,2,FALSE)))"
Copy cells B2 thru D2 down one line
Fill Down A2 to D3 thru row #102
Cell I2 - "5.0%"
Cell I3 - "4.5%"
Cell J2 - "18.250%"
Cell J3 - "18.125%"
Fill Down I2 to J3 thru row #92

Thanks in advance for any help,

Parker

I wrote a function in VBA called OpenSPDFiles:

i pass it those parameters and it opens the file(s) specified by the parameters.  in the end OpenSPDFiles equals a string
that is just the concatenated filenames of the files it opened.  if i dont pass an argument, a userform opens up and asks for
the data.

when I call it from another VBA code, it works fine - the files open. but if i go into an excel cell and type =OpenSPDFiles(... the files dont open. but the cell value DOES show the file names of what was supposed to open. AND, if i dont supply one of the params, the userform DOES show. why doesnt the function open the files? I use Workbooks.Open (sPath & sFile) to open. when i step through the code called from the excel cell, i see it highlight the Workbooks.Open but it just doesnt execute. any ideas?

thanks,
Joe

Hi All,

Strange problem today, I have been building some Vlookup functions and one has decided to not work so to investigate I built it bit by bit.

The formula is =VLOOKUP('Sheet2'!$M$1, '[Workbook2.xlsx]Sheet3'!$A$383:$Y$383,Column('[Workbook2.xlsx]Sheet3'!$W$2,0)

so it looks up a value from another sheet and finds it in column A of another workbook within a given range, then returns column W from that array. This formula works fine, however when I extend the array out to $A2:$Y$383 it suddenly loses the ability to find the correct data item.

Does anyone have any ideas as to why this might be occuring? and no there are no duplicate entries in column A and all other elements seem to be working fine with the smaller range?

Regards
Maudise

Hello,

For some unknown reason (to me at least), when I try to use the VLOOKUP
function now (on the same spreadsheet that I've been using for months), it
only shows the formula, and not the value that I'm trying to obtain.
However, when constructing the VLOOKUP function, when I fill in the
Lookup_Value, Table_Array, Col_index_num, and Range_Lookup fields, each time
I step through the fields, the values on the right of each respective field
ARE correctly shown. And down in the left corner, where it says "Formula
Result", that value is correct. But when I hit "OK", the only thing that
shows is the formula. Also, when I go the cell with the VLOOKUP function in
it, and right-click, and look in the "Sample" window, the value IS ALSO
correctly shown.

Cells that are using the VLOOKUP are formatted to General format.

Somehow, somewhere, I have changed a setting or something.

Why won't this work?

Thanks in advance for your replies.

Phil.
BTW, when I try it on a brand new file, it works fine.

Hi Everyone ,

I have 2 spreadsheets (attached) I need to pull gross salary info from one to another by using EmployeeID. I want to use Vlookup function in VBA However I'm totally VBA Newbie. I have found a code but it does not make sense to me now. I was wondering could you please help me to make necessary changes to get it to work?

Sub TestLookup()
Dim FileName As String
FileName = "C:yelizgross salary.xls"
adoLookup FileName, ActiveSheet.Cells(2, 1)
End Sub

Sub adoLookup(FileName As String, luValue As String)
Dim adoRs As New ADODB.Recordset
Dim adoConn As New ADODB.Connection
Dim query As String
Dim col As Integer

With adoConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
.Open
End With
query = "SELECT * FROM [Sheet1$A1:D6000]"
With adoRs
.CursorLocation = adUseClient
.Open query, adoConn, adOpenStatic, adLockReadOnly, adCmdText
.Fields(0).Properties("Optimize") = True
.Find "EmployeeID = '" & luValue & "'"
If Not .EOF Then
For col = 1 To 3
ActiveSheet.Cells(5, col + 1) = .Fields(col)
Next
Else
MsgBox "Employee " & luValue & " could not be found.", vbOKOnly, "Invalid ID"
End If
End With
End Sub

Regards
Yeliz

Hello,

I could use help creating a vlookup function. I have attached a spreadsheet that is an example of what I am trying to do. The main data is on Sheet 1, and the reference data is on sheet 2.

I need help creating a function (I was told Vlookup may be the way to go) in which the function will search Sheet 1 for each of the codes listed in column A of Sheet 2 and return the associated value from column B in Sheet 2 to Column R in sheet 1.

Any help would be greatly appreciated. I have been working at this forever and can't seem to get the formula right.

Hi all,

I am trying to set up a Vlookup function that will reference the date given in cell A2 against those given in another workbook and return the value in the 3rd column. The following code works fine.

=VLOOKUP(A2,'ServerJobCostsFiles[Rates.xlsx]JoeBloggs'!$A:$C,3)

The problems arise when I try to use a cell's value as the sheet name. The following code works fine, but only when the sheet I am referencing is in the same workbook.

=VLOOKUP(A2,(INDIRECT(B$1&"!$A:$C")),3)

I have tried to combine the examples above so that the cell value can be used to determine which sheet is referenced in the other workbook, but to no avail.

=VLOOKUP(A2,'ServerJobCostsFiles[Rates.xlsx]&(INDIRECT(B$1))&'!$A:$C,3)

Hope that all makes sense...Please help if you can, I've been trying to find a solution for days now.

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

Hello Guys: Was working with some VLOOKUP functions in excel for the first time but for some reason it gives the #NA value even though the exact lookup value is there in another file. Can anyone tell me why the VLOOKUP function does this and how I may get the matching value. The formula I used is

	VB:
	

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

HELLO
I AM HAVING PROBLEM USING IF FUNCTION , MY WORKSHEET LOOKS LIKE
A B C
CASH $4000 1910689
PAY OREDER $2000 2242530
CHEQUE $1000 1910668
The col A shows mode of payment col B shows amount received and in col C the deposit no in bank is shown, i would like to put these deposit nos (IN COL C)through if function because there are so many entries and it takes lot of time to fill each entry. Although VLOOKUP is working well but i would like to use IF Function , Can some tell me, how?
Thank you
Nabeel

I want to use a comboBox for the user to select a date. I had problems in getting the date to format in non-number format but the code below (from you site) works.
I then use the value (date) in the link cell (G2) as the first term in a VLookup function and it returns "#NA".
However, if I type a date into G2, the VLookup function works perfectly.
Can anyone help?
NB: I am using a Control rather than Forms ComboBox (why, I don't know!)
Des Murphy

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then
If IsNumeric(Left(ComboBox1, 1)) Then
ComboBox1 = Format(ComboBox1, "dd-mmm-yy")
Else: ComboBox1 = ComboBox1
End If
End If
End Sub

I am trying to use the vlookup formula and i cant figure out why it wouldn't work,

Here is the formula I am using.
in A2
=VLOOKUP(F2,Sheet2!1:65536,2,0) and it gives me back a #N/A But when I say =VLOOKUP(F2,Sheet2!1:65536,2,1) it will return a wrong answer.

Did I do any thing wrong here?


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