Vlookup Across Sheets, Nested Vlookup Possibly?
Jun 9, 2009
Im 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 months 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. Itd also be great if the formula can be transferred between workbooks. Im 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
ADVERTISEMENT
Oct 2, 2007
Currently I am using a vlookup for 19,000 columns or so. Basically what I am doing is calculating the change in the P&L values from when I ran the file currently to when I ran it previously. I have a macro that pastes the old data to sheet "PL Changes" and then the new data on "PL Detail" All the Vlookup is doing is taking 2 criteria(from A and E and comparing them to a concatenated formula in column A on "PL Changes" then returning the old value in column 5. Is Vlookup the most efficient or is Match() any quicker. Right now it takes almost a minute for it to calculate cells and I was hoping to reduce that time as this data may get longer: ...
View 9 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
Jul 13, 2009
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 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
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
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
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
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
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
May 20, 2014
I am trying to vlookup from sheet2 to sheet1. It gives incorrect values.
View 3 Replies
View Related
Apr 10, 2014
Vlookup between two sheets
I am trying to run a vlookup between two sheets
MY code is as below, In line 6 system is throwing an error as “RunTime Error 9” struggling to resolve it.
View 6 Replies
View Related
Sep 13, 2005
I have to do a vlookup, but the Table_array data is spread over two worksheets because of the amount of data.
View 12 Replies
View Related
Jun 6, 2009
I have a name in A14 in my overview sheet.
In some (not all!) of the other sheets the name is located in A48:A54 (only one of the cells but different from sheet to sheet). The corresponding value is located in B48:B54. I would like to return the sum of these values to B14 in my overview sheet.
Likewise I would like to return the values in column C in the other sheets to C14 in the overview sheet and so on until the sum of the values in AA are returned.
I might add several new sheets later on so I would appreciate a solution where I don't need to change the code each time I add a new sheet. VBA solution would be fine
View 9 Replies
View Related
Jul 24, 2014
I am trying to create a table which will search for a name throughout several other sheets. All of the same format and layout but with different data. I then need to display information in a different column of the same row of the name I'm searching, just like vlookup.. I also need to be able to search for multiple entries with the same name, so there may be 3 different entries for John Smith thoughout the few dozen sheets.
View 2 Replies
View Related
Jan 13, 2014
I have several sheets named by months like 1213, 1113, 1013, 913, 813, 713, etc (month-year) and I'm doing one sheet as an index that needs to find a value across all the sheets.
All the months sheets have a list of names in column A and status in column B. When the business with the name is done, it doesn't appear in the next sheet.
For example "Client A" could appear in 713, 813, 913 (business done) and won't appear in 1013, 1113 and 1213,
In the index sheet I want to lookup what is the status for a name from the latest sheet (last updated status).
So I have all the sheets names in a range called months and I want to make a small formula if possible.
I want to start looking in the latest month (sheet) and if that doesn't have any value it goes to the lower one and so on.
If I use =VLOOKUP($A$5,INDIRECT("'"&MAX(months)&"'!A:D"),4,0) it shows N/A since the name only appeared until 913 and its looking on 1213.
Right now this is working, but it's too big to understand.
IF(NOT(ISERROR(VLOOKUP($A$5,INDIRECT("'"&MAX(months)&"'!A:D"),4,0))),VLOOKUP($A$5,INDIRECT("'"&MAX(months)&"'!A:D"),4,0),
IF(NOT(ISERROR(VLOOKUP($A$5,INDIRECT("'"&MAX(months)-100&"'!A:D"),4,0))),VLOOKUP($A$5,INDIRECT("'"&MAX(months)-100&"'!A:D"),4,0),
[Code] ......
View 8 Replies
View Related
May 16, 2014
I'm trying to create a VLOOKUP that will return a value in 'Tracking' from 1 of 4 different sheets. If the value isn't found in sheet 1, the lookup should continue to sheet 2. If not found in 2, move to 3 and so on. In cases where there is no value, I would like to return " ".
Trying this:
=IFERROR(VLOOKUP(A2,'1'!A:P,2,FALSE),IFERROR(VLOOKUP(A2,'2'!A:P,2,FALSE),
IFERROR(VLOOKUP(A2,'3'!A:P,2,FALSE),IFERROR(VLOOKUP(A2,'4'!A:P,2,FALSE)," "))))
This formula returns values only in sheet 1. It won't continue to 2 and so on. Also, this will return a 0 for a blank value. If working as intended, 'Tracking' will show "g" in B2 and " " in C2.
View 7 Replies
View Related
Apr 15, 2009
I have an excel sheet that contains employee details (Emp. code, Name, Dept. & Shifts for the month) on one sheet (named Manpower) and their respective shifts on other sheets. The emp code in the manpower sheet should be looked up in the other sheets and the corresponding shift on the particular date should be returned. The problem is that since the sheets are arranged by departments the emp code has to be looked up in all the sheets till a value is returned.
View 3 Replies
View Related
Apr 22, 2009
I have another query now, similar to the one above. This relates to our Purchase invoice board.
All of our invoices are internally numbered, the info is entered onto the attached spreadsheet. A register, source of all Purchase information. (this sheet was not created by me by the way, its really old and my manager does not want to change it )
I would like to create a spreadsheet of the invoices that i have placed under query, i have set out a simple template at the moment which i use. But i have to input all the info from the invoice on this sheet, I can't help but wonder if the vlookup functon would work on for this.
I would like to enter our internal invoice no into my query spreadsheet and with the vlookup function i would like to retrieve the info from our purchase invoice spreadsheet
Only thing is, our invoice num are continously rolling throughout the year. New numbers are not created for the month, it continues from the last invoice number. However our invoices are filed on a monthly basis (hence the month tabs below).
Is there a way that a lookup function can be retrieve info from several worksheets at the same time in a different workbook?
View 11 Replies
View Related
Apr 30, 2006
i have to make it to where when i put a number in it also puts in the description and
unit price just by the number i put in to a cell. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong
View 9 Replies
View Related
Aug 21, 2006
I need to write a formula that looks for a value from column a, find the
sheet with a matching value in cell e4 and returns the value of cell ac1 of
the worksheet.
This formula will live in column b of the summary sheet.
Example:
Summary Tab
A B
1 19-Jul 19,000(formula result)
2 16-Jul
3 23-Jul
4 30-Jul
Data Tab 1
E AC
1 9-Jul 19,000
It will need to look in cell E1 of every tab in the work book (52 tabs) for
the value found in $a1.
View 9 Replies
View Related
Oct 6, 2008
I have a list of cusips/numbers in one file and I need to see if this cusip/number is in another file which has three worksheets. So tried this formula and what is wierd that if the cusip is in the first tab it returns #N/A, but if it's the 2nd tab it returns the right answer but if it happens to be in the third tab I get "false" instead. Why only if it's in the 2nd tabe does it return a value? I would combine all of these worksheets but it exceeds the number of rows allowed in the version I have.
I'm pasting the formula...hoping someone can point out what is wrong. I'll break it up to make it easier to read.
=IF(ISNA(VLOOKUP(E7,'P:Global InvestmentsPerformance ReportingDATASAC (new)Unclassified[SAC08_Unclassified_SeptMonthEnd.xls]Unclassified'!$B$2:$B$7137,1,0)),
IF(ISNA(VLOOKUP(E7,'P:Global InvestmentsPerformance ReportingDATASAC (new)Unclassified[SAC08_Unclassified_SeptMonthEnd.xls]Other Other'!$B$2:$B$25964,1,0)),
...........................
View 3 Replies
View Related
