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?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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..
View Replies!
View Related
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?
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
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!
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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).
View Replies!
View Related
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))
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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?
View Replies!
View Related
Time Replying Wrong
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
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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)))
View Replies!
View Related
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.
View Replies!
View Related
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" ........................
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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!
View Replies!
View Related
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
View Replies!
View Related
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
View Replies!
View Related
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..
View Replies!
View Related
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 -
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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?
View Replies!
View Related
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.DisplayAlerts = False 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
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
|
TRACKER
