Nested Vlookup The First 4 Digits In Column
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
ADVERTISEMENT
Apr 30, 2014
I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.
=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)
View 12 Replies
View Related
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
Mar 6, 2009
I would like to take the first three digits of column A and do a lookup of column B that would return the corresponding number from column C. For example, if I entered the formula for 103PH, the lookup would find the 103 in column B and return "2775.00" from column C...........
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 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
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
Oct 31, 2008
i need to format my numbers in the following format
10,00,000.00
the first three digits will be separated and then subsequently 2 digits
View 2 Replies
View Related
Jun 7, 2014
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
View 2 Replies
View Related
Apr 15, 2014
I have a list of project numbers in column "J" and i need to pull the first six digits of the project number to column "O"
View 4 Replies
View Related
Jul 9, 2009
I know how to remove the first part of a text, but not the end. I have a very large amount of data in which the first column contains a product description and then the UPC code. I need to isolate the product name from the UPC code into 2 columns. I used =RIGHT(B2,14) to isolate the UPC, but since the length of the product name varies, I'm not sure how to isolate the product name. I have looked at using the TRIM and TRUNC functions without any luck. 'Text to columns' doesn't work because I have multiple spaces, 'Text to columns' would work if I could used 2 digits as the delimiter, but the function doesn't allow that.
Can someone please suggest a function or formula for this?
Here is an examples of how the text appears in the field. (Note: I only need to cut off the UPC code, I need the measure information to stay).
some really long product name 12 oz 5 bag 09-99999-99999
another shorter name 2 oz 09-99999-99998
View 6 Replies
View Related
Jul 30, 2013
i have 2 coulmns in a listbox. The columns cant display more than 11 digits. So basically it would look like this 1.23456789E+12. I even tried playing with the Columnwidth but that didnt work.
View 2 Replies
View Related
May 7, 2014
I need a lookup formula that matches first three digits of column, U (153 - LINE PIPE), and match Total 13153 - LINE PIPE, 7th, 8th, and 9th digit in column C. and bring value from either column T,
The formula that I am using is not obviously not correct, I am putting it here to show what I am trying to do.
{=INDEX($T$2:$T$16,MATCH(LEFT(S2,3),MID(C2:C273,FIND(" ",C2),3)),FALSE)}
View 5 Replies
View Related
Mar 29, 2014
I basically I have a column with numbers. All the numbers are positive integers. What I like to do is have a VBA function that extracts the integers with the largest number of digits. So for example if we have the following column:
12
123
234
12346
2345
[code].....
So basically we search for largest number of digits, and extract the numbers that fit this category, which could be just one number or multiple numbers.
View 5 Replies
View Related
Sep 25, 2009
I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell. Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).
Is there a way to remove this vintage year form the string?
to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.
examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05
DuToitskloof Pinotage/Merlot/Ruby Cabernet
1999 Tradition Juracon 375ml
I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.
I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))
As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).
I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!
View 14 Replies
View Related
Sep 25, 2009
I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell.
Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).
Is there a way to remove this vintage year form the string?
to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.
examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
2003 Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05
I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.
I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))
As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).
I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!
Does anyone have an idea on how to help with this?
Ideally I would love to cut the vintage year, whether 2 or 4 digit, whether on right or left of cell and paste it in another cell, so to avoid manually doing it.
However, this is surely too complicated to do, so iwould settle with just deleting the vintage year and manually typing the vintage in another cell.
View 9 Replies
View Related
Jul 16, 2009
I am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)
View 2 Replies
View Related
Jan 18, 2010
I am trying to use a nested IF to enter info into col A. It is based on 4 pieces of info, I can get the first 3 to work but am struggling with the last part. The problem is that I am looking at the same cell for the first 3 parts but the last part is looking at another cell reference. A nested IF may not be the way to go. An example of the spreadsheet is as follows:
A B C D AB
1 12/1/10 Comp 1 Surveyer 1 18/1/10
Omit 11/1/10 Comp 2 Surveryer 1 Omit
0 10/1/10 Comp 1 Surveyer 2
Comp 3 Surveyer 1
The formula I am currently using in column A is:
=IF(AB3="Omit","Omit",IF(AB3,1,"0"))
This works for the first 3 pieces of information but however does put a 0 in col A if there is no date in col AB, but this what I expect as it's what I've asked for. I am missing the last piece of information.
But what I want to put in column A is:
If there is a date in column AB - enter 1
If there is the word 'Omit' in col AB - enter Omit
If there is no date or the word Omit in col AB - enter 0 and finally
If there is no date in col B - leave blank (if there is no date in col B, col AB will always be blank)
View 5 Replies
View Related
Dec 17, 2008
Column A contains an alphanumeric string up to 50 characters in length. I need a formula to search the string in Column A for the word "Appeal" and place that word in Column B when found. Likewise for the words "Grievance" and "Complaint". If none of the three words are found in Column A, the contents of Column B should be blank. Here's the formula I've played around with (it works on the first line but returns #VALUE! on following lines):
=IF(SEARCH("Appeal",A1),"Appeal",IF(SEARCH("Grievance",A1),"Grievance",IF(SEARCH("Complaint",A1),"Co mplaint","")))
I've tried entering it as an array, but that also fails. This seems like a pretty simple problem ,but I've spent several hours trying to make the formula work so I'm obviously doing something wrong.
View 4 Replies
View Related
