Tracker
HOME    TRACKER    Excel

# VLOOKUP Returning Wrong Value

## I have this formula that returns a grade mark for a specific score in a test: =VLOOKUP(D4,'Grade scale'!\$A\$34:\$B\$42,2) using these values in the lookup table: Low scoreGrade 0.0% U 25.0% G 35.0% F 45.3% E 55.3% D 65.5% C 72.0% B 78.0% A 85.0% A* The problem comes where the Grade is A or A* as the Vlookup table can't differentiate the two. Anyone know how to modify the formula so that A and A* are seen as distinct values?

Related Forum Messages:
Vlookup Returning Wrong Data
If the item in the vlookup table Array does not exist, the vlookup returns the result of an unrelated item. Is their another formula I can use?

IRR Function Is Returning The Wrong Value
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.

Match Formula Returning Wrong Row Number
I have a spreadsheet, with a Match Formula, that appears not to work. It looks at cell F1, and scans through cell C2:C14, and want it to return the relevant row in Column B (My work network will not allow me to upload the file). Cell F1 is the name of the sheets within the workbook (one for every month). However my data currently only has Apr - Jun, but when I choose month May, the formula feturns Month June.

VLOOKUP - Pulling Wrong Data
I have a VLOOKUP, which uses a named range called Department. The first column is sorted in asending order. The department numbers are 4 digits, formatted numeric. The department numbers are in order up to Department #19000, then they jump to 50000 series. Here's the problem, the 20000 department seris is in the master database, and the lookup is returning the data relating to Department #19000, instead of NA.

Vlookup: Pulls The Wrong Data Until A Correct One Occurs
I am having problems with the function vlookup. I attached an excel file of my problem. It seems to pull the correct lookup, then it pulls the wrong data until a correct one occurs. My excel attachment should make more since of my problem. It will also pull data that is invalid. For example, the name "S5" is not a in my original data so it should not be able to find that?? Sorry, this is hard to explain - let me know if the file is not clear enough.

Returning 0 Value With VLOOKUP
I'm hoping someone can help. I've written a =VLOOKUP formula that works fine. What I'd like to do is if the value can't be found within the lookup then the formula returns a 0 rather than the standard #N/A. Can these be joined up in 1 formula?

Vlookup Returning #N/A!
I have a problem with a table, works fine for the first five rows then doesn't work afterwards. Can anybody see what I'm doing wrong?
(File attached)

VLookup Returning #N/A
I have a VLOOKUP formula that is working just fine in Column O.. When I drag it over to Column P (changing nothing in the formula only changing the month) why would it now return #N/A. There is nothing wrong with the source that I can see..

Vlookup Returning #value!
=IF(ISERROR(VLOOKUP(B14,'Metal Worksheet'!\$A\$8:\$L\$157,11,FALSE)),"",VLOOKUP(B14,'Metal Worksheet'!\$A\$8:\$L\$157,10,FALSE))*D14

As long as the lookup value is true the formula works but if the lookup value is false then I get #value! when it tries to multiply by D14. How do i get the formula to return a blank cell or 0 value if the lookup value is false?

Vlookup Formula Returning #N/A
I have the following Vlookup formula:

=VLOOKUP(G3,'Calculation for September 07'!C7:E1975,2,FALSE)

The formula is returning #N/A, but I see the lookup value in the table array.
What do I need to add to the formula to have the lookup value recognized?

VLookup, Returning A Value On A Vector
I want to use the V lookup function to return a value, but rather than returning the value in the same row as the match in the specified column, I want to return the value in the cell below.

Eg.

=VLOOKUP(B397, A\$267:H\$351, 7, FALSE)

... but rather than returning the value in column 7 on the row that matched with B397, I want the field below.

Vlookup Keeps Returning #ref
i have a drop down list in cell b16 which then corresponds with what to bring up in the next drop down list in cell d29 this works fine.

Vlookup Returning #N/A When Value Exists
I feel like I've tried everything on the attached spreadsheet to get the vlookup to pull back the schools code.

Vlookup Not Returning A Value (vba)
the following vlookup is not returning a value in the specified cell (C39). From the threads I have searched through it looks OK but not working

Sheets("Day 1").Range("C39") = Application.WorksheetFunction.VLookup(Sheets("Day 1").Range("M2"), Sheets("PCO").Range("B1:C12"), 2, False)

Im sure from past experience I have missed a comma or something but buggered if I can find it!

Using A Vlookup And Returning A Cell Reference
I have a sheet which uses a vlookup to find the data on a large sheet. normally to get the cell reference of the data i would use cell("address",......

However this appears not to work with a vlookup.

Vlookup Returning #N/A When It Shouldn't
i sell solar panel systems and i've broken it down to 18 different packs that i can sell, the reason for this is that i can very quickly have a tailored quote for a particular client. what i want to do is is literally from a pull down menu have the 18 packs, pick one and for all elements of that quote to come up. i have used a data validation list for the pull down menu which works fine. for the 18 packs, each one has an array of info of between 15 - 18 rows and 4 columns wide (containg info like part description in first column, then cost in second column, quantity in third column and total cost in fourth. how i set it up was, i have all the seperate packs going for left to right on a spread sheet with the title name on top. i have created a quote area so when i use the pul down menu to get title all that pack info should come up. i have done this by using the hlookup command to return the first column on the left - description on parts and this works fine - then to return the cost in the second column, i am using a vlookup command by looking for the description on the left to return the cost. this works for 90% of the info and for the other 10% i get an #N/A when i know it shouldn't. all cells have the same format so i know this isn't the problem. i would really appreciate any help on this from anyone who has had this problem before.

Doing A VLOOKUP And Returning Multiple Entries
I've tried to amend the formula's posted above but to no avail so am hoping someone can have a look at the attached.

I would like to be able to use Vlookup or similar to complete a table (starting in cell A19) based on the surname added in B16.

Vlookup Returning The Closest Match Down
I'm using vlookup to get a price for a unit of measure and it passes the value of the lesser not the greater. Is there anyway to get vlookup to pick the closest match as the greater value not the lesser one?

For example:
1000 units costs \$8,000
4000 units cost \$23,000
12000 units cost \$68,000

If someone wants to buy 2500 units, I'd like the vlookup to pass the price based on 4000 units, but it passes 8,000 the value on 1000 units.

Can I use vlookup to give me the result of the greater unit not the lesser?

Vlookup: Returning Multiple Matches
I am trying to connect a special date and a price to a special name (please see the attached file).

In sheet 1 I have the following:
- In column A I have a set of different names.
- In column B I have a set of different dates.
- In column C I have a set of different prices on bonds that off course refers to the different names at their respective dates.

In rest of the sheets I want an overview of the different bonds, and what their prices are at the exact date. The problem is that if I use the vlookup it is only the first value that will be extracted. How can I get all of the prices corresponding to the different dates to the correct bond. I also want the possibility to update this every day.

If/vlookup Function Returning N/a Error
My problem is that I am getting the N/A error in cell M3 if L3 is empty. I would like the forumla to 'ignore' cell L3 if it is empty. I do not want a zero put in there. I have looked up information on ISBLANK function but the more I read the more confused I am getting

Vlookup - Referring To A Column Other Than The First And Returning Text
I have two worksheets. On one worksheet will be a place to enter a five digit number. In the adjacent cell, I want to search on the second worksheet in a three-column area for that same five digit number. The five-digit numbers on the second worksheet are located in the third column and the text I want returned from the second column. I cannot change the order of the columns as the first worksheet will be an "add-on" worksheet to a widely used spreadsheet within our company (this spreadsheet already has the second worksheet formatted).

Formula Cell To Be Blank (Vlookup Returning #N/A)
If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?

This is the forumal I am currently using
=VLOOKUP(A26,Subs!A:F,5,0)

I have also tried this one as well
=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))

Returning Text From Nested IF And Vlookup Statements
I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.

Example: ....

Vlookup Values In 2 Columns Returning Value From The Third Column
My Data

Service 1 | 100 | 1.5\$
Service 1 | 200| 2.5\$
Service 1 | 300| 3.5\$
Service 2 | 100| 11.5\$
Service 2 | 125| 21.5\$
Service 2 | 300| 30\$

I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.

I tried SumProduct but the problem with my formula was:
Let's assum I ask for
Service 1, 200

The formula summed up 1.5 and 3.5 and returned 5

When I wanted to return one value from this table.

Vlookup Preventing From Returning Smaller/other Values
I have a table of data, one column is for the names of all these branches of our offices, that consist of 2 alpha numeric digits. For instance one branch is 6G, then another is 6F, 8G, etc. The other column has a raw number in it. So I'm doing a vlookup for the first column to return the value in the second column. But, vlookup automatically returns a value thats the next one over if it can't find the vlookup value. For instance, if it can't find 6G it'll just give me the number it finds for 6F. But I don't need that! I just want it to return either a zero or n/a if it can't find 6G.

Vlookup = Lookup Returning Previous Row Cell Data
I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.

For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.

VLOOKUP - Returning '#N/A'
I have just decided to dable in the 'VLOOKUP' function. I have looked at a few examples on the net and I have tried to replicate them with no luck (I have modified titles and values). I have attached my example to this post.

VBA - What Is Wrong With My For Each Next
I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!

Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range)
Dim Roundname As String
Dim Result As Double
Dim NameColRound As Integer
Dim ListObject As Variant

Roundname = "Round " & Round
NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0)
For Each ListObject In NameList
If ListObject.Value = Name Then
Result = Result
ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then
Result = Result + 1
End If
Next
maxbarometer = Result
End Function

Getting Wrong FinalRow
when i try to get the final row witht the following:

KROGrow = Cells(65536, 1).End(xlUp).Row

i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?

Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time.
In the example in the sheet the load time is 01:00
The start time is 23:00
The end time is 23:35

Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late.
Does anyone have a formula to slove this issue

Formula Returns Wrong Value
The formula below was written a couple of years ago and I just discovered an error. It returns an "S" for the value 0789 when it should be a "C", (Consecutive).

IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},\$O215:\$R215,0))),AND(ISNUMBER(MATCH(MIN(\$O215:\$R215)+{0,1,2,3},\$O215:\$R215,0)))),"C",IF(SUM(IF(FREQUENCY(\$O215:\$R215,\$O215:\$R215)=3,2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY(\$O215

******** ******************** ************************************************************************>Microsoft Excel - FL MID PLAY 4 CONSECUTIVES.xlsm___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutT215=OPQRST215078907893SSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Saving In Wrong Location
I have the following code which saves my worksheet with a name extracted from particular cells:

Public Sub SaveAsMaximoWO()
ThisFile = Range("AC5").Value
ThisFile2 = Range("E3").Value
saveName = ThisFile & " - " & ThisFile2
ActiveWorkbook.SaveAs Filename:=saveName

End Sub

This (for reasons I don't understand) saves the file to My Documents by default.

What I would like it to do is save to another folder within my documents as default.

Wrong Week Number
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?

I am using the following formula, could someone explain this formula in
english?

=CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))

Date Is The Wrong Century
I'm trying to parse a text file into Excel and the date is coming out as "1940" instead of "2040". In the text file the date reads "5/14/40". I've already changed my regional setting in Excel so that if I type in "5/14/40" it is understood to be "5/14/2040". For some reason this doesn't work when I parse the data though.

Data Is Going Into The Wrong Cells
The macro was orininally set up for the 7 days of the week. Now I want to set it up for the 31 days of a month. It's set to pull information from a register sales journal. Here is the button macro-

Sub Button2_Click()
Dim sDay As String
Dim sShift As String

sDay = InputBox("Which Day? (1 For MONDAY, 2 For TUESDAY, ... 7 For SUNDAY)")
Select Case sDay
Case "1"
sDay = "1"
Case "2"
sDay = "2"
Case "3"
sDay = "3"
Case "4" ........................

References Goes Wrong After Sorting
I have a problem with my references when sorting in excel. I have been searching for the problem, but did not found a useful solution.
I'm using excel 2003.

Let me try to explain my problem with a simple exampel.

I have a column with numbers and one with formulas pointing at these.
A B
2 =A1
4 =A2
3 =A3

Now I want to sort column A, and the column B should keep pointing at the original number. So I want this:
A B
2 =A1
3 =A3
4 =A2

But I get this:
A B
2 =A1
3 =A2
4 =A3

This output is what I would expect if I used absolut reference(\$).

Using offset(B1;0;-1) will work, but I cannot use this, since I will add/delete rows and the reference is not neccessarily next to it. Plus it can be a rather large sheet.

I cannot use dynamically name definition either, because it is intended for other users, and this will be too much work for them. What I basically need is a simple formula they can enter.

My sorting is done via a VBA macro, so if there is anything I can do through macro/VBA that will be okay as well, since I can do this without the user seeing it.

Decimal In Wrong Place
how to control this,it should be 0.55. is they a wat too round off too next highest hole number? Here is a example.

Click On Wrong Button
I have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:

I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.

Pivot Table Subtotals Are Wrong!
With the following data Excel's pivottable's subtotal shows underlying MAX total instead of subtotals relating to values displayed.

SalesRepOrderNoItemValue
ACON12334
ACON12322
ACON12412
ACON12424
ACON1248

Pivottable:

Sum of ItemValue
SalesRepOrderNoTotal
ACON12356
AC ON12444
AC Max34

Rather than use SQL (I don't have data on SQL server) or re-organising data by order number, is there a fix using calc items, calc fields or grouping to show correct MAX figures? Why Excel doesn't simply evaluate visible cells seems crazy to me!

Filtering, Wrong Rows Removed
I have a module that imports data from a text file (120000 rows), filters and removes some unneeded (85000)rows and then puts the data in a designated workbook.

Problem: when filtering by some criteria it for some reason removes a row off the final file for each criteria. I have a total of 23 filtering criteria. I have attached a file for exemplification. You can run the module and import the file, it will be without first 23 rows, even if non of the deleted rows contained the filter criteria.

example.txt

Error If I Enter Wrong Criteria
The following code works like a dream for me, but if was to enter the wrong letter, not "N" or "R" and i tried to delete it, a debug message appears?

Is there a way to allow any letters to be deleted?

Here is my code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 Then
With Range("C" & Target.Row & ":R" & Target.Row).Interior
Select Case Target.Value
Case "R": .Color = RGB(184, 204, 228)
Case "N": .Color = RGB(120, 120, 120)
Case Else: .Color = RGB(255, 255, 255)
End Select
End With
With Target.Font
Select Case Target.Value
Case "R": .Color = RGB(184, 204, 228)
Case "N": .Color = RGB(120, 120, 120)
Case Else: .Color = RGB(0, 0, 0)
End Select
End With
End If
End Sub

Cells At Wrong Position After Converting
i have converted my text from notepad into excel format and i realise that some of my columns are shift 1 cell away for example my applications name should be in cell A but after converting, some of my cell A is move into Cell B. is there anyway that i can delete the cell that are blank and shift to Cell A or shift everything from cell b into cell a if cell a is blank? i have alot of documents to change..

Rowfilter Reads Wrong Values
I have a rowfilter on the location (so i can view who took the test by location nad whether or not they passed) - the only problem is that when it selects by location - it chooses the correct name, personellnum, location row but it then picks the test values ABOVE that row - not below it - so It is choosing the complete wrong values to go with the associated person -

Formula Evaluate To The Wrong Conclusion
In cell A1 is 13.00, the result of a formula.
In cell A2 is the formula =IF(A1>14.00,True,False)
The result of this formula is TRUE
In cell A3 is the formula =15-A1
The result of this formula is 2

why the formula in cell A2 would evaluate to the wrong conclusion? All cells are formated as numbers.

LOOKUP Returns Wrong Or Odd Value
I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?

Mod Function Works Wrong
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.

And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.

So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1

But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6

Pivot Table Subtotals Are Wrong Instead Of Zero
I've just started using Pivot Tables and have a problem right away. Couldn't find anything with the search function, but am not really sure, what to search for, either

Part of my data looks like this

NameLot #BinAmountABC123CB18C174.025 ABC123CB18C-24.975 ABC123CB18C-24.975 ABC123CB18C12.616 ABC123CB18C-12.616 ABC123CB18C-24.975 ABC123CB18C-24.100 ABC123CB18C-25.000 ABC123CB18C-25.000 ABC123CB18C-25.000

Now this is a material that is stored on a pallet with the code CB18C. The total sum of material that is added to/taken from this pallet adds up to Zero. My Pivot Table, however, believes the total is something like 2.84217E-14.

Sum of Amount　　　NameLot #BinTotalABC123CB18C2.84217E-14　123 Total2.84217E-14ABC Total　　2.84217E-14Grand Total　　2.84217E-14

This problem occurs several times, the raw data adds up to Zero, but the Pivot Table calculates these tiny, tiny totals. Is this a known bug or can Pivots not handle 3 decimal data?

Statement Effecting Wrong Ranges
clearcontents is clearing some cells outside of the specified range. Some of the cells in row 10, which is above the beginning row of specified range, are also getting cleared. I've tried several variations to no avail.

Here is the code I am using:

Sub ClearDataSheets()

Application. ScreenUpdating = False

Dim d1 As Date, d2 As Date

d1 = Date
d2 = Worksheets("FPY").Range("A375").Value

If d1 <= d2 Then
MsgBox ("Resetting the program prior to start of new year is not permitted."), vbExclamation
Exit Sub

Else

Macro Gets Info From Wrong Worksheet
I have a userform which is designed to get and enter data into one worksheet.

When I call the userform up from a different worksheet it doesnt refer back to the data source worksheet?? How can I do this?

I have used

sheets("dataworksheet").activate
in the user form initialise event but this still doesnt work.

Code Is Selecting The Wrong Sheet
Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("SUMMARY")
Set ws2 = Sheets("WIP")

Application.ScreenUpdating = False
With ws2
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select ' Clear
End With

x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
The code is run from ws1. I put in the .select for now because I don't want to loose my info on ws1.

I trying to clear the range A2:C2 all the way down to where info is before doing the Do Loop which will repopulate the same range from ws1.

If the rest of the Do Loop code is needed, let me know.