Nested Vlookup Not Return Correct Value?
Feb 20, 2014
I have the following formula:
=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?
View 3 Replies
ADVERTISEMENT
Jun 9, 2009
I’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
View 14 Replies
View Related
Nov 12, 2008
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.
View 4 Replies
View Related
Mar 26, 2009
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.
View 11 Replies
View Related
Feb 11, 2009
I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:
I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)
=IF(ISNA(MATCH("N06*";Sheet1!AI:AI;0));"error";INDIRECT(ADDRESS(MATCH("N06*";Sheet1!AI:AI;0)*1;1;1;T RUE;"Sheet1")))
I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.
To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.
View 3 Replies
View Related
Mar 20, 2014
I am trying to make this work. VLOOKUP (B2,"lookup tables'!$A$4:$A$11, if(D2'="ST" 'lookup tables!$C$4:$E$11.
I am attaching the file : MDCC Reservations.xlsx
I am trying to get the Daily room charge.
View 5 Replies
View Related
Nov 7, 2007
Level RatingA RatingB RatingC Low Medium High
2 x x x x x x
3 x x x x x x
4 x x x x x x
5 x x x x x x
I need to be able to do nested loops. I want to have a criteria that if a person is in LEVEL 2 and RATINGC and HIGH, then take the value under that.
View 9 Replies
View Related
Sep 24, 2009
I'm trying to build this formula and I'm only half way through it and it already isn't working.
Column D - can be 1, 2, 3, or 4
Column E - can be any number
If column D is 1 or 3, then the VLOOKUP should look up column E and point to the answer in column 4 on another worksheet (Companies in this case). If column D is 2 or 5, then the VLOOKUP should look up column E and point to the answer in column 5 on another worksheet (Companies in this case). This is what I have so far, just trying to get it to recognize if it is a 1 or 2:
=IF(OR(D2=1,VLOOKUP(E2,Companies!A:G,4)),IF(D2=2,VLOOKUP(E2,Companies!A:G,5)))
What is making this not work? I'm getting a false. But before I added the OR, it worked for the D2=1.
View 3 Replies
View Related
Apr 7, 2014
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.
View 13 Replies
View Related
Mar 4, 2014
How to get correct value using vlookup formula in duplicate look up values.
Here i mentioned a eg;
VlookUp_DuplicateValue.JPG
View 8 Replies
View Related
Aug 1, 2009
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.
View 5 Replies
View Related
Apr 11, 2012
I have done a VBA course but am struggling to apply it to my situation. What I want to do is on one spreadsheet, I have a column with "shift, date, grade 1, grade 3, quarantine" in it, and in the cells next to it I want an operator to enter either "day" or "night" next to shift, and the date. Next to the others I want the total weight of the appropriate product made in that time. The spreadsheet I will be feeding off is quite big with a lot of information in it. Down the left hand side is the roll number (we make rollstock). Further along on that same row will be the date, the weight, and a grading (putting it into grade 1, 3, quarantine etc). Our shifts run from 7am to 6:59pm, and 7pm to 6:59am. Which shift the roll comes under is determined by it's end time (also in that row). So basically, I need VBA to say sum up all of column [weights] if column [date] = [date cell in working spreadsheet] and if working spreadsheet says day then column [roll end time] =7:00-18:59 and if working spreadsheet says night then column [roll end time] =19:00-6:59 and also if column [grade] = [grade cell in working spreadsheet] ....
Oh, also on the feed spreadsheet i have a column that i put in a little code date, "AP04" for 4th of april kind of thing, rolls being made before 6:59am on the 5th of april being put into the 5th april as they need to be counted under 4th april as they are 4th april night shift (even though the date will stay 5th april)...
View 3 Replies
View Related
Feb 20, 2009
Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"
I have 2 worksheets.
Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)
Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg
ABC PTY LTD 5671
ABC PTY LTD 5683
ABC PTY LTD 5692
ABC PTY LTD 5610
ABC PTY LTD 5611
Tried the below formula
=if(C1='S2'!A2),=if('S1'!A2='S2'!B2),"ordered","")
View 8 Replies
View Related
Oct 8, 2009
Hi guys, hopefully you can shed some light onto this small problem I am having.
The current formula I'm trying to use is:
=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))
This isn't producing the desired results.
What I am trying to do in words is this:
IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail....
View 7 Replies
View Related
Oct 13, 2008
I have a new worksheet:
Employee No. Name
Under the Name colum (B) i want a formula that uses the employee number (A) to look up the name. The problem i am having is that i need to look the name up from 3 other worksheets.
On worksheet 1 i have fridays work and 2 i have saturdays work and 3 sundays!! I only want a name to appear if they worked any one (or more) of these days. If they didn't work i would like the name to remain blank.
On all worksheets employee number and name arein colums A and B.
I do have another worksheet that has name and number only in though. worksheet 4!
View 2 Replies
View Related
May 25, 2014
I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.
Setup Sheet
Class Number
Number of Arenas Used
Class 1
[Code].....
View 3 Replies
View Related
Jun 26, 2007
In the example below I have created a validation list in B1 that references D2:D8. What I am then trying to do for cell B2 is create a vlookup and nested if statement that says if b1 is a number lookup the value in column D and return the value in colunm E and if b1 doesn't = a number, ie 'none' then return a null value. The same goes for B3 where if b1 is a number lookup the value in column D and return the value in columm F and if b1 doesn't = a number, ie 'none' then return a null value.dropdown list problem.
************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1Numbernone*NumberDescriptionRef2Description**none**3Ref**1one44***2two55***3three66***4four77***5five88***6six9Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
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.
View 3 Replies
View Related
Feb 8, 2013
=HLOOKUP(G8,'Headline Assumptions'!$C$32:$F$37,E8,FALSE)
Above formula, in cell E8 is the number 3, however excel does not recognise this and returns 1. is there a way i can get E8 to see that it is a 3 in that cell and return the correct number of rows downwards in my table?
View 1 Replies
View Related
Feb 12, 2009
I have attached a small sample of some data I am working on (the total is about 6000 lines overall spread over 30 worksheets), but I am stuck trying to get a nested vlookup to work.
What I have
A list of codes contained in 'A' and values in 'B'. I have grouped together the codes in colum 'A' starting with the same 4 digits, and gave them a named range. Columns G and H show all the possible range names. 'K' is a list of all the seperate codes (I know it is the same as 'A', but this is just an example to get a formula working)
What I would like formula column L
to lookup the first 4 digits in column 'K', use that value to lookup the range name in 'G & H', then using the FULL code in K, look for that in the corresponding name range and return the value from 'B'
View 4 Replies
View Related
May 22, 2009
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)
View 3 Replies
View Related
Feb 12, 2009
I have filled the following formula in many cells of a spreadsheet.
=IF(ISERROR(VLOOKUP(D75,'Sheet1'!$D$3:$D$5442,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75,'Sheet1'!$C$3:$C$54 42,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE)),VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE),VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE))))
I am wondering if I have nested too many functions for this to work properly. In the case where one of the first two if statements are true (an error would be produced), I am receiving a "0" as specified. However, in the case where the statement VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE) results in an error then I should have the return of VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE), but am getting a #REF! error instead.
View 3 Replies
View Related
Jul 26, 2006
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: ....
View 14 Replies
View Related
May 19, 2009
I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.
For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:
View 2 Replies
View Related
Nov 6, 2011
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?
How can I change this number for the actual Date?
View 9 Replies
View Related
Feb 1, 2007
I have three named ranges that I need to do lookups for First I need the formula to do a look up at the HUMANALOOKUP range and then if a value is not found go to LABLOOKUP and then if not found go to CHCSLOOKUP and if not found return a blank. the column layout of each worksheet is the same so the column index number same throughout the formula.
View 2 Replies
View Related
May 13, 2013
Here's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
View 1 Replies
View Related
Jun 12, 2009
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:
“The List Source must be a delimited list, or a reference to a single row or column”
What should really happen is this:
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.
View 9 Replies
View Related
Aug 23, 2008
I have the following vlookup working exactly as I want it to in excel: =IF(ISNA(VLOOKUP(LEFT(C2,10)& "*", NAME,2,FALSE)),"",(VLOOKUP(LEFT(C2,10)& "*",NAME,2,FALSE))) -NAME is a defined section on another worksheet
The problem I have is when I come to use this within a vba macro I'm writing the wildcard section automatically gets spaces added so it goes from "*" to " * " and excel doesn't like it! The code I'm using to write it into the cell is simply:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)),"",(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)))"
how to stop the spaces being added?
View 2 Replies
View Related
Jun 24, 2009
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies
View Related
