# Vlookup Formula Is Working On Some Criteria But Not Others

Jul 18, 2009The vlookup formula is only partly working on the attached sheet, but I cant find an explanation as to why it can look up some criteria but not others.

View 3 RepliesThe vlookup formula is only partly working on the attached sheet, but I cant find an explanation as to why it can look up some criteria but not others.

View 3 RepliesADVERTISEMENT

I am trying to get this tricky formula to work with vlookup and if error. The problem is that excel will not recognize the formula when I try to drag it down, but instead repeats the same numbers over again. I've tried some combinations using vlookup, rows, and if error, but it doesn't seem to work. So I'm trying to drag down the formula from g3:g99, to reference either cells b8:99, or whichever value I enter into f1, on down to b99.

View 4 Replies View RelatedI have a sheet set up with names. I have 2 other sheets with spouses and

children that belong to the first sheet. I want to insert a formula that

will look up the correct spouse (based on a member number) and put in it the

column. Same with the children. I tried the vlookup function, but it did

not return the correct result.

This vlookup has been in this workbook for years. All of a sudden any of the cells that have formulas won't work without placing the cursor after the formula in the bar and hitting enter.

It is keeping a marco from running correctly.

I have a vlookup formula: =VLOOKUP(TRIM(C101),Sheet2!$A$1:$B$500,(2), FALSE)

It is not working with General number values. Why is this? Is there something I should add?

The formula works if I swap columns to one with text or if I include text in the column.

currently my formula is as follows:

=VLOOKUP(H42,POQntyTOTAL,5,FALSE). i need to add another criteria to this formula.

that is....i need to have it return column 5 from POQntyTOTAL, where H42 AND where E42 is found.

I am trying to modify an existing nested vlookup formula to include one more condition. I attached the excel data file. There are two tabs:

Tab #1 - Performance

Column B (Email Send Date): can be a repetitive date, something like

Row 2. 4/25/2014

Row 3. 4/25/2014

Row 4. 4/25/2014

Row 5. 4/25/2014

Row 6. 5/2/2014

Row 7. 5/2/2014

Row 8. 5/2/2014

Row 9. 5/9/2014

Row 10. 5/9/2014

Row 11. 5/9/2014

Column F (Product ID): can be same product for different Email Send Date. For instance, Row 2 & Row 9 have the same product ID - 128 and Row 5 & Row 10 have same product ID - 131.

Row 2. 128

Row 3. 129

Row 4. 130

Row 5. 131

Row 6. 567

Row 7. 897

Row 8. 987

Row 9. 128

Row 10. 131

Row 11. 234

Column R: Units Sold - need to retrieve the units sold value from Column D - Units Sold in UnitsSoldOnlineVlookup tab.

The formula needs to lookup the Units Sold from a table in a different tab, named UnitsSoldOnlineVlookup. This table contains the following columns:

Tab #2 - UnitsSoldOnlineVlookup

Column A - Email Send Date

Column B - Product Description

Column C - Product ID

Column D - Units Sold

Before Product IDs were different for each Email Send Date and I successfully used this formula:

=IF(ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,

VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))

Now I need to embed one more condition to this formula - lookup Units Sold for the Product ID as well as the email date:

lookup Units Sold for a Product ID for a corresponding Email Send Date in UnitsSoldOnlineVlookup table and return Units Sold into the corresponding cell in the Performance tab.

I thought to use MATCH function in addition to IF and ISERROR functions but I it doesn't work - I know it is wrong.

=IF(MATCH(B2,UnitsSoldOnlineVlookup!$A$2:$D$31000,0),

ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,

VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))

Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.

agent rank score supervisor

A B C D

1 leo 1 99 jim

2 bob 3 68 jim

3 sam 1 73 ann

4 tim 2 84 adam

5 alf 2 55 ann

6 dan 3 42 adam

7 will 2 79 jim

8 sara 1 91 adam

9

10 Rank: 1

11 Supervisor: adam

12 Agent: " "

I have 3 sheets ("zip", "report", "ches") in a workbook

I want "ches" column A to be filled with data from "report" column D if there is a match of "zip" cell A12 from "report" column S

I keep getting the error

"unable to get the Vlookup property of the WorksheetFunction class"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range

Dim myRange1 As Range

Set myRange = ActiveWorkbook.Worksheets("report").Range("A1:S65536")

Set myRange1 = ActiveWorkbook.Worksheets("ches").Range("a65536").End(xlUp).Offset(1, 0)

myRange1.Value = Application.WorksheetFunction.VLookup(ActiveWorkbook.Worksheets("zip").Range("a12"), myRange, 4)

End Sub

I have a vlookup that returns an #N/A error due to the table array originally being formatted as text. The data is simply a two or three digit number.

I have tried everything I can think of to get the table array to equal the lookup value.

I formated cell to number. I tried tab delimated/finish. I tried typing a 1 in a different cell and doing a paste special then multiply. I tried copying to a different worksheet. I just cannot get it to work.

VLOOKUP will only work to a set limit number of text characters in a cell. So I can search for the value "*orange*" if the cells contain a reasonable amount of text, but as soon as this exceeds some limit, it returns BLANK. I've also tried using INDEX/MATCH with same problem. Is there a workaround or alternative function?

For example:

This will work and return the text in cell A2

A2: The quick brown fox jumps over the lazy dog. Orange. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog

This will return BLANK

A2: The quick brown fox jumps over the lazy dog. Orange. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog

Sheet 3 column c is where the formula is located - pulling data from b14master (sheet)

Worksheet attached

I have problem with vlookup. I am attaching my file.

View 3 Replies View RelatedI have two worksheets and VLookup is not working between the two worksheets. I made sure that type of text is same . COntent is trimmed to avoid any blank spaces. If i put the formula for the same sheet range i get the value but for another worksheet i m getting #NA error.

[CODE][=VLOOKUP(A4,CddInfo_Filtered!A1:E6,5,FALSE)/CODE]

I use Vlookup all the time but this is one has be stumped. On the attached spreadsheet I trying to lookup the description on sheet 2 (lookup data) and return it to description on sheet 1 (orig data). I have the both fields formatted as text so I'm not sure why it's not returning the value?

View 5 Replies View RelatedI have the following formula that has ceased to work in excel. I don't think there are any errors in it so I'm unsure as to why the link no longer works. Neither of the files I'm using has moved location on the server.

=IF(H7>0,(I7*H7)+((I7*H7/100)*VLOOKUP(B7,'T:FILEFILEFILE'!$B:$E,4,FALSE))+0.25,0)

I'm not sure if this makes much of a difference but people opening the file use various versions of Microsoft Office.

I have 3 worksheets for 3 separate managers, each sheet has a column which uses a COUNTBLANK function to work out the number of days off an employee has had off in the last two weeks:

Now the formula works perfectly on each worksheet, however I also have a master worksheet (which is identically set out and formatted to the 3 manager sheets) which uses a vlookup formula to pull the data from all 3 worksheets into one at the beginning of the workbook.

The problem I've come across is that the COUNTBLANK formula doesn't work on the master worksheet - I think this has something to do with the vlookup formula entered into the cells which I consider to be blank, but excel doesn't?

I've tried using a separate vlookup formula to pull the data from the 3 managers worksheets and this does work unless an employee appears on more than one managers' sheet - then the data from both worksheets is added together.....

Is there another function I could use to display the information I need to?

I have a spreadsheet with members details in and I've added a few columns showing:

Col Q: Paid 2008 (blank if No, 1 if Yes)

Col R: Show Paid Yes or No (shows a Yes or a No)

Col S: Date Joined (only entered if new and joined during 2008)

Col Z: Subs owing from 2008 (if existing members and owe last yr's subs)

Col AA: Subs to Pay in 2009 (a standard £35 or pro rata if joined during 2008)

Col AB: Pro Rata (if they joined other than at the beginning of 2008)

Col AC: EA Sub? (based on a yes/no column, to add £5 if yes)

Col AD: Total to Pay for 2009

So the formulae (or data entered) for the above columns are as follows:

Col Q: Paid 2008 - blank if no, 1 if Yes

Col R: Show Paid Yes or No - =IF(Q2=1,"Yes","No")

Col S: Date Joined - a date entered in format d/m/yy

Col Z: Subs owing from 2008 - =IF(R2="no", 35,0)....I want a default 35 in here

Col AA: Subs to Pay in 2009 - =IF(S2="",35,"Pro Rata")....says that if there is no date which indicates they are an existing member, they will pay the standard 35, otherwise they'll be paying a pro rata fee...................

Problem:

It all works fine until a certain row (27 actually) - then I get a VALUE! error which points to the Date Joined field. Now I've tested that value against the vlookup table and it returns the correct data, so why doesn't it do it in my spreadsheet????? I've tried changing the formats on the cells; clearing all data; entering a different date, but from that row in the spreadsheet down to the last row, it simply won't work! I've looked at each formula in each cell on the rows where it is working and the rows where it isn't and I can't see anything different.

What else can I look for? It is SO frustrating as it does what I need it to to do but only for a third of the spreadsheet!!!!

I have a column of numbers I want to look up, and a column of results to the right I want to spit out.

So I have the vlookup formula in column G, H a copy of that, except it's showing the formula instead of result. Same thing with I and J. So you can see the actual formula.

I've manually found the first two numbers and colored them red and green... but I can't seem to get the formula to work.. As you can see, it's only able to find the negative numbers, but not any of the positive numbers.

I tried to test the cells if they are equal or not, and they seem to be equal!

embedding the following

(VLOOKUP(A2,[svdlsa08_2007mast.xls]Sheet1!$A$2:$U$3869,1,FALSE)

in the following statement:

=IF(((VLOOKUP(A2,[svdlsa08_2007mast.xls]Sheet1!$A$2:$U$3869,1,FALSE))-A2=0),(VLOOKUP(C2,[svdlsa08_2007mast.xls]Sheet1!$C$2:$U$3869,17,FALSE)),"No Match")

The IF statement is designed to check a different value in the source spreadsheet against one in the destination sheet which should always match for the desired values, but this doesn't seem to make any difference to what is being returned - I'm still getting the first match instead of the appropriate one.

Is there a better way to force the VLOOKUP to return the value from the correct match and not just the first one?

I'm trying to compare two very large files to find some that are in one AND also in the other. I'm using =VLOOKUP(C2,Parts2!C:D,1,FALSE) and my results are #N/A for all of them. I have formatted both columns as 'General' because there can be letters and numbers in the serial number, I have also tried formatting as text to no avail. I have confirmed no preceding or trailing spaces or other special characters.

I am however, able to locate values in both documents using Ctrl F. I'm totally puzzled because I've always been able to figure out what's causing my Vlookup issues when I have them but this time I'm not able to.

I am trying to use a =vlookup(and( type formula. Excel accepted my syntax, but the formula is not working. Here are the details:

=VLOOKUP(AND(E2,O2),'[SOURCE DATA FOR PROFILES.xlsx]Sheet1'!$B:$G,6,FALSE)

The values from cell E2 and O2 are both present on a row in the SOURCE DATA spreadsheet, and column 6 is the last column in my table (both values appear before column 6, but not next to each other).

The formula returns #N/A. Under those circumstances, shouldn't it return the value of column 6 on the row where those two values are present together?

Do the columns containing the values have to be next to each other in the table for this to work? Does the data type matter (one is a number, the other a street address)? The value to be returned is a number.

I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.

Cells A1 to B5 contain a lookup table:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}

.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}

LetterNumber D4 C3 B2 A1

Cells A7 to C8 contain the input values:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}

.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}..................

Normally, sumif formula works well with problems that have one criteria to take into consideration. With the table that I have, I'm unable to accurately use SUMIF. I'm not even sure if I should use it since what I really need is Average. SUMProduct proves to be even harder to use for me. Here's what I have:

ID CODEStatusSTARTDUEENDTURN AROUND TIME (TAT)

122307Closed1/142/81/3112 DAYS

122265Closed1/142/22/214 DAYS

122265Closed1/142/101/3112 DAYS

122307ATD1/131/21----------

122253Closed1/121/221/229 DAYS

122265ATD1/221/30----------

Other details:

I used =SUMPRODUCT(--($B$21:$B$31=C4),--($D$21:$D$31=$D$3)) to count the total number of a specific task status per ID code, say, closed and ATD.

I manually added TAT that has "Closed" status on a per ID code basis.

Ex:

ID Code# of Closed StatusAverage TAT

12265213 DAYS (14+12/2)

Is there a formula that will give me the average TAT taking ID Code and "Closed" status as conditions/criteria? What about Vlookup, average?

How should my multiple If And Or statement syntax read?

On first button click If "BUY" or "SELL" is in column 83 then copy as the code indicates to sheet "Orders" as the code indicates. This it does.

On second button click don't copy the same "BUY" or "SELL" that has already been copied to sheet "Orders".

So the Countif should set J to greater than 1 for the previous copies and be ignored and only copy from sheet "Main" the new "BUY" and "SELL" that show up.

I'm getting double and triple copies of items already copied that should set J to > 1.

Code:

Option Explicit

Sub BuyCells()

Dim c As Range

[Code].....

Excel 2013

Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:

=VLOOKUP(A2, '[Shipping Report.xlsx]Monthly shipping schedule'!$A$1:$F$5000, 6, FALSE)

Also, the jobs are arranged by ship date, not by number order.

This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.

Here is a sample of my spreadsheet:

Ship Date

1325 12/27/13

1326 3/10/14

1327 1/4/14

1328 1/31/14

1329 3/3/14

1330 1/22/14

1331 1/15/14

1332 #N/A

1333 #N/A

1334 #N/A

1335 #N/A

1336 #N/A

1337 #N/A

1338 #N/A

1339 #N/A

1340 #N/A

I have an Excel 2003 situation I cannot resolve. In one workbook, I have a table of multiplication factors based on a numerical general ledger code. On another sheet, I have a data extract including that code and a cost. In the latter sheet, I need to multiply cost by the appropriate factor based on the GL code - IF Code=X, then cost*factor.

Should be simple, but I have too many codes for a nested IF statement. I also tried a VLOOKUP, but since the codes are numerical, Excel interprets them to be in the thousands, while my factors are 1.xxxxxxx, and since the data being looked up thereby appears smaller than the keys, VLOOKUP will only return #N/A or ?NAME, depending on whether I try to nest it in an IF or not. I also have several thousand records per month to which this needs to be applied, so going manual is not an option.

The calculating is done in column E, the original cost is in column AB, the key (GL code) is in column R, and the GL code range with factors is on the !Factors sheet (column A=Code, B=Description, C=factor).

*I'm running Excel 2003 on WinXP Pro

I have a drop down list called Status including Testable and Not Testable

A1 = Testable

A2 = Testable

A3 = Not Testable

A4 = =COUNTIF(A1:A3,"Testable")

Gives me 0... i don't get it. Should show 2 no?

I think to run one report. First sheet put options and copy to data page as criteria, and run advanced filter, it only shows heading.

VB:

Sheets("Report").Select

Range("23:350").Delete

Sheets("Details").Select

Range("w8:ae9").Copy

Range("w12").Select

[Code] ......

I am trying to create an equations that sums up a range based on matched dates. I have considered using an array, but that turned out to be much more work than necessary.

I have never used the SUMIFS before and having trouble, here is the equation I'm using.

=SUMIFS($F$2:$F$204238,MONTH(E2:E204238),"=month(referenced_range)")

*F2:F204238 is the range that needs to be summed up

*the month(referenced range) is in date form

*I was able to pull and evaluate all the data on it's own, but when using it in the sumif equation it gave me zero result so it must be a syntax error.

Copyrights 2005-15 www.BigResource.com, All rights reserved