Vlookup: Pulls The Wrong Data Until A Correct One Occurs
Jul 13, 2009
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.
i have created a Vlookup that pulls data from another work sheet, it all works fine apart from this one thing....
On a work sheet i have a lot of hyperlinks that work fine, but as soon as they get pulled over using a Vlookup, the name of the hyperlink comes accross for example "link 1" but you can not click on it to take you to the link like you can on the original one.
I am working on a form to pull employee identifiers such as employee ID, store number as well as sales performance. I am using the below formula but the data that is being returned is not the correct data for the specific employee. C5 is the employee name (last name, first name) in a single cell. A sample of the spreadsheet I am trying to pull data from is attached. Some employees the formula pulls the right data and some it does not...
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.
Is it possible to get Vlookup to pull the format of text across to the new cell?
For example, if the source data is bold, can you get it to show up as bold in the new cell? Or worse, if there are multiple formats in the original cell (like some text is bold, some is red font etc.) can that be pulled across through Vlookup?
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
##################### 149 entries Column A - Date - OK Column B - Customer_Phone - Errors (Should be 11 digits) Row 21 - Customer_Phone - Error (Not 11 digits) Row 108 - Customer_Phone - Error (Contains letters) Column C - Outcome - OK Please correct and re-check. #####################
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
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?
I have a list of data, integer values ranging from 0 to 36. Imagine a Roulette wheel. The list is long, with over one hundred data points. I would like to view the first 14 data points, and count how many times each value occurs. How many 0's? How many 1's? How many 2's?...How many 36's?
Obviously, many values will have 0 occurrences. Most will have 1 occurrence, some 2, and maybe one or two will have 3 occurrences. I doubt we will see a value with 4 or more occurrences, but it is possible. With this accomplished, I will then note the results. So, that accomplishes the first 14 data points, call them 1-14.
Then, I want to move down the list 1 data point, and repeat the process. So, here I am looking at data points 2-15. Basically, it's the same set of data, with the first point missing, and a new point added on. I will then note the results. I want to continue doing this until the last 14 are viewed.
I have a VBScript that pulls SNMP data from some devices. It uses two external EXE's called "SNMPGET.EXE" & "SNMPWALK.EXE". When the script runs, it pulls specific information from our SNMP devices (in this case, I am pulling temperature values from Netboz Devices). The script looks like this :
I use a lot of VLOOKUP formulae in the sheets i put together and, despite both the lookup value and table array being in the same format (usually text format), i often get an #N/A being returned.
Here's the fun part... if i then do something simple like access the lookup value cell (either by double clicking or pressing F2) and then hit return, then the vlookup calculation suddenly returns the correct value.
I'm convinced it's something to do with the way the cells are formatted but can't work out what.
Looking for some help to figure out what is causing our problem. My friend has created the attached excel sheet in Excel 2003. The issue is that the a vlookup does not seem to be finding the correct line for the value from the lookup table when it is returned twice.
Let me explain - The details are contained in the attached workbook. There are three sheets in the workbook - NH3Curve, Samples and Qvalue table.
On the Samples sheet, in cell C12 and C13 you can enter varying values. Go across to F12 and F13 and you will see that they both have the value 22.1. This is where the issue occurs - in cells G12 and G13 the lookup value should be the same - but it is not. G12 is actually returning the value for 22.0 not 22.1. (lines 173 and 174 on the Qvalue sheet). We cannot ferret out why this is happening.
Any help would be greatly appreciated. Sometimes when you look at something you can't see the obvious, so I hope it is that simple.
I need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
=IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)<(VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)-((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03))),'CORE Data 3 Month #2'!A3,IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)>(VLOOKUP('CORE Data 3 Month #2'!$A:$K,6,FALSE))+((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03)),'CORE Data 3 Month #2'!C3,'CORE Data 3 Month #2'!B3))
The strangest thing is happen. The part of formula underlined and the part bolded are identifical. For some reason, excel is unable to find the value in the underlined portion but is able to find the value in the bolded portion. As a result, I'm getting a #N/A result. how this can be corrected?
People usually keep track of their own points, however an official would need to confirm that they are correct before approving a purchase. However 99% of the time, people have the incorrect point totals.
I am currently working on a book to automate the system. It means players wouldn't have to keep track of their own points and that the point totals are always up to date. The first sheet in this workbook is a summary page which has player names, points from posting, bonus points, level up costs, purchase costs etc. Then there is a database with all the costs, which I pulled from the site. Then there are numerous record sheets, which have the player name, the item bought/levelup purchased, and the cost, which is pulled from the database via vlookup. Here is where it gets irritating.
On the summary page I attempted to use SUMIF in the total fields (total cost of purchases, total cost of leel ups etc). It would basically search the record for any entries with the name matching that of the player, and add the amounts associated with that entry. However it would always return 0. After doing some playing around I came to the conclusion that whilst the VLookup returns the correct values, functions like sum see them as 0. So instead of it going 5+7+7+10=29, it says 0+0+0+0=0.
See attachment for full explanation of problems. I'm trying to get the correct sales tax when selecting a particular county. I can't get the vlookup to work and also I can't get the formula in the data validation of the county to work.
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate() Dim sBook As String On Error Resume Next Application.EnableEvents = False Col = 8 ' Sets start column Do sBook = Cells(2, Col) ' References file name TaskRow = 6 ........................
I have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
I have two worksheets in one document. On sheet 'M_Admission' there are numbers for each week. can those numbers be automatically copied to the correct cell on sheet 2 'M_Actual' See the attached Excel worksheet to see what I mean. (Excel 2002)
I created an UserForm that looks for Student Name his/her Conferenece Date through a VLookup.
The following is my code:
Private Sub cmdFind_Click() With frmConference .txtTranslator.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 6, 0) .txtDate.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 5, 0) .txtTime.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 4, 0) End With End Sub
The txtTranslator and txtTime works correctly, however txtDate give me a number '40850' not a Date?
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
I have a table with 3 columns of dates and then a column with Set # that I feel in the box #.
I need to see how many items processed for each set per day.
Example: [url]
The problem is that it counts the correct amount but not with the correct dates. The formula that I use is: =SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
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" ........................
Above is what I hav already, I was thinking it would help if each card had an additional field added that told you if the contents were valid. This could also say empty if all fields were blank. This field could for example use an if statement to check that you have entered a VS, a start date a project name. It could also check that if you have entered a completion date you also enter review loops. This could be in large red text so it is obvious to someone completing a card that they have not yet filled in all necessary data.
My colleague and I are writing a udf which uses a "match/ index" combination to return a value to a cell from a named range in another sheet. The cell returns returns the error "value used in the formula is of the wrong data type". When we break down the code into a function which refers directly to the relevant cells, and a sub which returns the value in a message box, it works correctly: here's the code for the function and test sub:
Function MRL(PropertyType, ReturnType, Location, Year2) ....
