# VLookup Function - Incorrect Results

Nov 10, 2011
I have a problem with VLOOKUP function and I am looking for a workaround.

SheetA I have a column of business names and another column of the category.

SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...

View 7 Replies
ADVERTISEMENT
Jun 5, 2014

If a player does not qualify, their name and score should not be included in the final results.

I currently have their Name missing, but the NUMBER is showing up.

I've explained more in the attachment.

INDEXARRAY.xlsx

View 3 Replies
View Related
Nov 3, 2009

If I type in an age in A1, in A2 I want an age group to appear

the groups are

20-24

25-29

30-34

35-39

40-44

45-49

50-54

55-59

60-65

so far, if I type in an age then it normally enters the group ABOVE what I want

i.e. if I enter “24” then it put the group “25-29”. if I enter “42” it puts “45-49” and so on.

=LOOKUP($A1,{0,16,20,25,30,35,40,45,50,55,60},{"<16","20-24","25-29","30-34","35-39","40-44","45-49","50-54","55-59","60+"})

View 3 Replies
View Related
Aug 4, 2006

My goal is to sum up the total sales and total forecasted sales of each cost center. I compiled a formula that is supposed to do this just as I want it to. Simple enough. However, for some odd reason the formula returns either the totals of ALL cost centers or just a zero instead of the correct result. The weird part is that when I check the formula by pressing the "insert formula" button it suggests that the formula actually should produce the desired result.

View 5 Replies
View Related
Jan 20, 2014

I'm trying to do a simple conditional format where the date is highlighted in red if the difference between that date and Todays date is greater than 90 days.

I've done the formula as =(TODAY()>$B5)>90, which appears to work in the sheet and shows either True or False but when inputting that formula into the conditional format it highlights every single cells.

Are conditional formatting formulas different?

View 5 Replies
View Related
Sep 4, 2012

Using Excel 2007.

I setup a named formula: Spread1 =Sheet1!$E$2:$BE$2 where I entered % values to spread a an annualized figure over 53 weeks

On Sheet 2 I entered the amount to spread in $D$2 and in $E.. I entered my formula: =Spread1 * $D$2. Everything appeared great!

But then I deleted a few columns on sheet2 so first calculation is now in $G$2

Spread1: =Sheet1!$E$2:$BE$2

Calculations: Sheet2! $G2:$BG$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)

Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).

How I can defeat it with the possibility that a user may delete a column?

View 4 Replies
View Related
Nov 28, 2012

I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.

My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.

View 4 Replies
View Related
Aug 30, 2007

In the attached spreadsheet I'm using VLOOKUP to create a cross reference between worksheets JS and ITEM. If you will look at the ITEM worksheet cell reference H13 & H14. The correct value for H13 should be AMC, not 729. Is there a way to use the value in the Class column and Item ID column in combination to get the value AMC? Would MATCH & INDEX work? I'm not familiar with Match & Index. I'd appreciate some help here. I've got 15,404 records to evaluate this way.

View 14 Replies
View Related
Aug 22, 2007

I'm trying to create a new function in Excel and for some reason the syntax is incorrect and it won't let me use it in the sheet. I do not know what I'm doing wrong so any help would be appreciated. This function will allow me to interpolate the term structure of interest rates between dates (I think) My code is

Function INTSPOT(spots, year)

'Interpolates spot rates to year

Dim i As Integer, spotnum As Integer

spotnum = spots.Rows.Count

If Application.WorksheetFunction.Count(spots) = 1 Then

'Single rate given

INTSPOT = spots

Else 'Term structure given

If year = spots(spotnum, 1) Then

INTSPOT = spots(spotnum, 2)

Else

Do

i = i + 1

Loop Until spots(i, 1) > year

INTSPOT = spots(i - 1, 2) + (spots(i, 2) - spots(i - 1, 2)) * _

(year - spots(i - 1, 1)) / _

(spots(i, 1) - spots(i - 1, 1))

End If

End If

End Function

View 9 Replies
View Related
Feb 22, 2007

I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?

View 3 Replies
View Related
Apr 22, 2008

I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.

View 9 Replies
View Related
Mar 7, 2014

I am trying to create the following function but I cannot seem to get it working correctly.

[Code] .....

View 3 Replies
View Related
Aug 20, 2014

I have a macro which returns the results for

=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))

As of today, the entry EMPLOYEE is sometimes written as it was EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?

Before it was:

ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"

I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+

"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"

as I would in Excel, but of course it doesn't work.

View 3 Replies
View Related
Mar 4, 2009

I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.

=VLOOKUP(A8,MA!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,PQ!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,ON!$A$8:$H$19,3,FALS E)+VLOOKUP(A8,TW!$A$8:$H$22,3,FALSE)

View 11 Replies
View Related
Oct 11, 2006

I wan to create a table using 2 column of data.Below are the data

Type Name

A Danon

B Chris

C Hilton

A Jin

A King

B Ling

C Seng

D Aaron

B Halim

I wan to convert these data to a table which have four dimension. A, B, C, D. With Vlookup function, i can do it. But, i find difficulty when the result is overlap, when in the type A, got Danon, Jin, and King. My result only can lookup is King. Other cannot display.

View 4 Replies
View Related
Apr 9, 2014

Worksheet1 has data.

Worksheet2 uses VLOOKUP back to worksheet1 for data to populate cells on worksheet2.

The VLOOKUP command below returns a value of 126 to cell C6. I want to use the number result of the VLOOKUP in C6 (126) as part of a calculation for another cell. In thiscase, cell D6 is keyed in. I want to get the result of =(N(D6)-N(C6)) and put it in cell F6. To further complicate things, if C6 is blank, I want F6 to be blank.

VLOOKUP formula that returns a value of 126 from worksheet1 and puts it in cell C6 on worksheet2. It also returns a blank if there is a blank on worksheet1.

=IF(ISNA(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0))+(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0)=""),"",VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,FALSE))

View 3 Replies
View Related
Dec 10, 2013

Add together D3+G3+J3+M3+Q3+U3 and put the result into W3? I think the reason it wont work is because there will not always be a value in D3/G3 and J3/M3

New KS4 grades Markbook.xlsx

View 5 Replies
View Related
May 8, 2012

I have a large spreadsheet of data, with lab results by date. Sometimes, there are multiple data points for a single date.

I need to produce monthly reports that list all the results for specific tests in a given month.

So, for example:

Date SG Property Tank

1/5/12 1.015 567324 2044

3/15/12 1.002 568210 2103

3/18/12 1.025 568056 2044

3/18/12 1.036 565200 2102

4/1/12 1.019 566713 2103

I would like to make a report for March that looks like this:

Date SG Tank

3/15/12 1.002 2103

3/18/12 1.025 2044

3/18/12 1.036 2102

And the next month, make a report for April, then May... etc.

I tried to think of a way to do this using vlookup or index/match, but couldn't figure it out.I also tried using filters and then automating some kind of copy/paste, but there is an extra line between the heading and the data, so that the date column is filtered as text instead of date. The source data is not my spreadsheet, so that would be difficult to change.

View 9 Replies
View Related
Dec 17, 2012

I would like to do a lookup function, in which multiple results are returned.

i.e.

VLookup Result 1

Result 2

Result 3

Etc.

I know how to do it if there is only one answer but say I have a unique identifiers in which I want all the results in column B:B displayed one after another, say locations have numerous products sold/manufactured at it.

View 1 Replies
View Related
Apr 22, 2013

I have a Sheet named Main, Juz and another called wordforword.

Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.

I do prefer formulas over macro if possible.

View 1 Replies
View Related
Sep 20, 2012

I am looking for Vlookup function, which returns multiple values without duplicates.

Please find the attachment : Vlookup Unique.xlsxâ€Ž

View 3 Replies
View Related
Jan 22, 2014

I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.

As a example i have attached the sheet.

View 1 Replies
View Related
Jan 8, 2009

I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.

Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.

View 4 Replies
View Related
Dec 8, 2009

Hi, I'm sure this will be an easy one for most of you. I need to extract data from an array for each time I have recorded a specific vehicle registration. I have used VLOOKUP but this only gives me one result. I need excel to display every enrty for the registration I am looking for. I have attached a sample file which should make more sence.

View 7 Replies
View Related
Dec 18, 2009

i put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.

View 4 Replies
View Related
Oct 25, 2013

I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used

=REPLACE('RAW DATA'!A3,1,2,"")

Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.

I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.

View 9 Replies
View Related
Feb 2, 2013

im trying to create a drop down menu that changes depending on the results of a vlookup. example: I have a vlookup function that populates a field with a product name based on the part number, however in some cases there are multiple products with the same part number. is there a way to create a drop down menu that contains all the product names shared by the same part number based on the part number that was entered?

View 1 Replies
View Related
May 8, 2009

I have a sheet that uses V lookups to return names, there are 10 diff names throughout the sheet it could return

I want to change the background colour of the cell depending on what the name is. the v lookup is run in collums A - L and 100 rows down

How can I code it so each name has its own background colour so the Cell changes according what the V look up returns?

View 12 Replies
View Related
Oct 22, 2009

I have a workbook where one tab contains the data that I am given:

Project-A data1 data2 date1 date2 date3

Project-B data1 data2 date1 date2 date3

Project-C data1 data2 date1 date2 date3

Project-D data1 data2 date1 date2 date3

In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.

So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.

View 7 Replies
View Related
Feb 12, 2009

I am using the VLOOKUP function to pull multiple columns of information from another sheet in the same workbook based on a name. The sheet it is pulling from is a query. I am looking up product names, and in the query there may be multiple results that the function can find.

VLOOKUP only returns the first result that is found. What I am trying to do is get it to dump all the results into a drop down list in one cell. If I attach a drop down to one of the columns of information I am retrieving, then I can select which of the entrys to display, right? I think this should work I just don't know how to go about setting it up to do it.

How would I set up a VLOOKUP formula that attaches a drop down list containing all the results that the function finds?

View 7 Replies
View Related