Using Left To Return Match 


Using Left To Return Match  Excel 
View Answers 
I think this is the usual bugbear of Excel's formatting but...
I am trying to match part of a string within a cell (the 2 digits on the left) to a range to return the cell position within that range. The formula I'm using is as follows:
={MATCH(LEFT(E9,2),$A$657:$A$681,0)}
It's returning #N/A.
I believe this is because of formatting since if I enter e.g. 11 in E9 (rather than use the LEFT function), the function returns the expected cell position. I've tried reformatting as number, as text, etc. But no joy.
Any idea how to circumvent this? Ultimately, I want to use the result of LEFT to MATCH to the array so I return a text description of the code returned. Obviously the above formula won't do this, I'll be using some other functions bolted onto the above to do that but since I've fallen at the first hurdle I'm concerning myself with this problem initially.
thanks
I am trying to match part of a string within a cell (the 2 digits on the left) to a range to return the cell position within that range. The formula I'm using is as follows:
={MATCH(LEFT(E9,2),$A$657:$A$681,0)}
It's returning #N/A.
I believe this is because of formatting since if I enter e.g. 11 in E9 (rather than use the LEFT function), the function returns the expected cell position. I've tried reformatting as number, as text, etc. But no joy.
Any idea how to circumvent this? Ultimately, I want to use the result of LEFT to MATCH to the array so I return a text description of the code returned. Obviously the above formula won't do this, I'll be using some other functions bolted onto the above to do that but since I've fallen at the first hurdle I'm concerning myself with this problem initially.
thanks
Similar Excel Video Tutorials
INDEX & MATCH functions Example 2!
 See how to use the INDEX & MATCH functions together when VLOOKUP or LOOKUP will not do the job! See how use the INDEX & MATCH functions togeth ...
Part 1: Return Multiple Items From One Lookup Value for Table w Formula
 Duplicate Lookup or Lookup 1 value, return many for a table Part 1. Return winning bets for week from a football betting database. See a formula that ...
VLOOKUP function formula 7 Examples
 Learn about:
1)VLOOKUP to lookup an exact match
2)VLOOKUP to lookup an approximate match
3)VLOOKUP can return a value to a cell
4) ...
1)VLOOKUP to lookup an exact match
2)VLOOKUP to lookup an approximate match
3)VLOOKUP can return a value to a cell
4) ...
VLOOKUP to Left (MATCH & INDEX or LOOKUP)
 See Mr Excel and excel is fun try to use VLOOKUP to the left. Mr Excel shows how to use INDEX & MATCH to solve this problem. Excelisfun will use t ...
Helpful Excel Macros
Vlookup Function That Searches The Entire Workbook  UDF
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Format Cells as Text in Excel
 This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to cells
 This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to cells
Format Cells as an Accounting Number in Excel Number Formatting
 This free Excel macro formats a selected cell as an Accounting number. This means that the accounting number format is
 This free Excel macro formats a selected cell as an Accounting number. This means that the accounting number format is
Return the ISO Standards Start of the Year in Excel  First Monday of the Year  UDF
 Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
 Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Similar Topics
in the following function the LEFT function is not resolving correctly. when i break the individual components out they resolve correctly, yet when i bring them together the LEFT returns a FALSE when it is in fact TRUE. any thoughts?
=IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT($C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,Repor tLine,0)))
what the formula is trying to do is:
1. look at B33 and determine if it is an account number or an account name
2. if it is an account number, it is to look up a range of account numbers.
3. if it is an account name (ie not an account number), it is then to look into C33 to determine if the account name comes from a "Profit" or "Report"; it does this by determining if C33 begins with a "P" or not.
4. depending on whether it's a profit or a report item it then looks up the appropriate range.
i will include an IF to return a "Valid", "Invalid" result accordingly, yet the LEFT and MATCH is failing to return the reference. at the moment B33 is text and C33 is "Profit".
=IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT($C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,Repor tLine,0)))
what the formula is trying to do is:
1. look at B33 and determine if it is an account number or an account name
2. if it is an account number, it is to look up a range of account numbers.
3. if it is an account name (ie not an account number), it is then to look into C33 to determine if the account name comes from a "Profit" or "Report"; it does this by determining if C33 begins with a "P" or not.
4. depending on whether it's a profit or a report item it then looks up the appropriate range.
i will include an IF to return a "Valid", "Invalid" result accordingly, yet the LEFT and MATCH is failing to return the reference. at the moment B33 is text and C33 is "Profit".
Hi all,
I'm trying to put together a formula that looks for the first blank cell in a range, then returns the Header Title from that row (this second part will come later). The problem I'm coming across is getting Excel to return the position this is. I'm using the MATCH function to return where this cell is. I've been trying:
Code:
But this always returns an N/A error. Does anyone know another means of returning the blank cell's position with MATCH or some other function?
TIA,
SamuelT
I'm trying to put together a formula that looks for the first blank cell in a range, then returns the Header Title from that row (this second part will come later). The problem I'm coming across is getting Excel to return the position this is. I'm using the MATCH function to return where this cell is. I've been trying:
Code:
=MATCH("",A2:S2,0)
But this always returns an N/A error. Does anyone know another means of returning the blank cell's position with MATCH or some other function?
TIA,
SamuelT
Hi, I am trying to do a left match formula:
In the following string, I need to identify the position of the zero, in relation to the last number of the right, in this case 6. So I would like the result to be 5, as the zero is five cells to the left of 6. In the same way that a match would tell me that the zero was 3 cells from the beginning left of the string.
1 2 0 3 4 5 6
Can anyone help??
Many thanks!!!
In the following string, I need to identify the position of the zero, in relation to the last number of the right, in this case 6. So I would like the result to be 5, as the zero is five cells to the left of 6. In the same way that a match would tell me that the zero was 3 cells from the beginning left of the string.
1 2 0 3 4 5 6
Can anyone help??
Many thanks!!!
Howdie, I am afraid that I need some help ...
I want to create a formula that:
1) checks to see if all the numbers in an array are used in a column
2) returns the numbers that were not used
I say "Sudoku", but it really (for what I need it for) does not matter if the numbers repeat, only that they are all there.
So, using "Sudoku", I created this formula:
=IF(ISNUMBER(MATCH({"1"},LEFT(C2:C10,1),0)),"","1")
&IF(ISNUMBER(MATCH({"2"},LEFT(C2:C10,1),0)),"","2")
&IF(ISNUMBER(MATCH({"3"},LEFT(C2:C10,1),0)),"","3")
&IF(ISNUMBER(MATCH({"4"},LEFT(C2:C10,1),0)),"","4")
&IF(ISNUMBER(MATCH({"5"},LEFT(C2:C10,1),0)),"","5")
&IF(ISNUMBER(MATCH({"6"},LEFT(C2:C10,1),0)),"","6")
&IF(ISNUMBER(MATCH({"7"},LEFT(C2:C10,1),0)),"","7")
&IF(ISNUMBER(MATCH({"8"},LEFT(C2:C10,1),0)),"","8")
&IF(ISNUMBER(MATCH({"9"},LEFT(C2:C10,1),0)),"","9")
Is there anyway to achieve this without having to do a long copy and paste job to incorparate a much larger array?
Would appreciate any input on the matter ...
I want to create a formula that:
1) checks to see if all the numbers in an array are used in a column
2) returns the numbers that were not used
I say "Sudoku", but it really (for what I need it for) does not matter if the numbers repeat, only that they are all there.
So, using "Sudoku", I created this formula:
=IF(ISNUMBER(MATCH({"1"},LEFT(C2:C10,1),0)),"","1")
&IF(ISNUMBER(MATCH({"2"},LEFT(C2:C10,1),0)),"","2")
&IF(ISNUMBER(MATCH({"3"},LEFT(C2:C10,1),0)),"","3")
&IF(ISNUMBER(MATCH({"4"},LEFT(C2:C10,1),0)),"","4")
&IF(ISNUMBER(MATCH({"5"},LEFT(C2:C10,1),0)),"","5")
&IF(ISNUMBER(MATCH({"6"},LEFT(C2:C10,1),0)),"","6")
&IF(ISNUMBER(MATCH({"7"},LEFT(C2:C10,1),0)),"","7")
&IF(ISNUMBER(MATCH({"8"},LEFT(C2:C10,1),0)),"","8")
&IF(ISNUMBER(MATCH({"9"},LEFT(C2:C10,1),0)),"","9")
Is there anyway to achieve this without having to do a long copy and paste job to incorparate a much larger array?
Would appreciate any input on the matter ...
Hello,
I'm relatively comfortable with Excel, but I've always used formulas in the past; now I'm starting to dip my toe into the VBA side of things. I have a formula that I'd like to convert, so that I can use it in a Macro:
=IF(LEFT(F2,1)=LEFT(G2,1),IF(LEFT(F2,2)=LEFT(G2,2),IF(LEFT(F2,3)=LEFT(G2,3),"YES")))
There are probably cleaner ways of doing it, but it basically looks at the values in F2 and G2 (which are text, if it matters), and compares the first three characters to see if they match.
I using Range.formula, but got errors:
Code:
Any advice as to either what I'm doing wrong (or a more straightforward way to do this)? Thank you!
I'm relatively comfortable with Excel, but I've always used formulas in the past; now I'm starting to dip my toe into the VBA side of things. I have a formula that I'd like to convert, so that I can use it in a Macro:
=IF(LEFT(F2,1)=LEFT(G2,1),IF(LEFT(F2,2)=LEFT(G2,2),IF(LEFT(F2,3)=LEFT(G2,3),"YES")))
There are probably cleaner ways of doing it, but it basically looks at the values in F2 and G2 (which are text, if it matters), and compares the first three characters to see if they match.
I using Range.formula, but got errors:
Code:
Range("H2").Formula = "=IF(LEFT(F2,1)=LEFT(G2,1),IF(LEFT(F2,2)=LEFT(G2,2),IF(LEFT(F2,3)=LEFT(G2,3),"YES")))"
Any advice as to either what I'm doing wrong (or a more straightforward way to do this)? Thank you!
Hi All,
I am trying to create a formula to do some conditional formatting. I need an 'alert' to show if an incorrect pairing has been done.....
In cell A2 i need to reference the first three characters (left(a2,3)="***" and then say, if it equals "***" then the first three characters of cell D2 should equal "***" Please note that cell D2 is complete free text.
The pairings i have are as follows:
CGM needs to match MON
CGL needs to match LAP
and
CGD needs to match DSK
I tried the below formula for one instance, but it would not work and I cannot work out why
=if((and(left(a2,3)="CGM",(left(d2,3)="MON")),1,0)
Would I need to create 3x conditional formatting formula, or could i tie all three together in one?
If someone can have a look at this I would be very grateful.
Thanks
Rich
I am trying to create a formula to do some conditional formatting. I need an 'alert' to show if an incorrect pairing has been done.....
In cell A2 i need to reference the first three characters (left(a2,3)="***" and then say, if it equals "***" then the first three characters of cell D2 should equal "***" Please note that cell D2 is complete free text.
The pairings i have are as follows:
CGM needs to match MON
CGL needs to match LAP
and
CGD needs to match DSK
I tried the below formula for one instance, but it would not work and I cannot work out why
=if((and(left(a2,3)="CGM",(left(d2,3)="MON")),1,0)
Would I need to create 3x conditional formatting formula, or could i tie all three together in one?
If someone can have a look at this I would be very grateful.
Thanks
Rich
I am trying to use the INDEX and MATCH functions to return a value in a table. The function looks like this: =INDEX($H$78:$AL$122,MATCH(B19, $H$78:$H$122), MATCH(B38,$H$78:$AL$78))
H78:AL122 is the table, and I want it to match cell B19 to a value in the left column, and match cell B38 to a value in the top row. Then the function should return the corresponding value in the table where these two intersect.
The function works fine, except that when the value in B19 is past the 33rd value in the left column (I found this out by trial and error), the function defaults to the last row in the table and gives me the value where the last row intersects with the proper column based on the value in B38.
Does this function have some limitation at the 33rd row? Or am I doing something else wrong? Thanks.
H78:AL122 is the table, and I want it to match cell B19 to a value in the left column, and match cell B38 to a value in the top row. Then the function should return the corresponding value in the table where these two intersect.
The function works fine, except that when the value in B19 is past the 33rd value in the left column (I found this out by trial and error), the function defaults to the last row in the table and gives me the value where the last row intersects with the proper column based on the value in B38.
Does this function have some limitation at the 33rd row? Or am I doing something else wrong? Thanks.
Having problems with a formula im using vb to assign to a cell.
I have developed the formula in excel and then i have copied it in the macro and made the appropriate changes (ie " = chr(34) )
The formula error in for cell AJ3
the other formulas prior work.
The length of the formula is approx 360 characters, which i thought was a problem but the formula itself working in excel, except when i try to use VB to embed it in a cell.
The comments prior to the formula Array are the actually formulas copy from the excel worksheet used to write the formula array in VB
In simple terms the formula as checking :
This sheet is a list of invoices to match against the database and update the invoice history.
These formula basically do a multiple match criteria and return a number xx.nn where xx is the sum of the row numbers that match and xx is the match count /100. I expect only to one match, but the nn is there is see if there is more than one match.
I am relucant to include the workbook because i has some much other crap in it but may need to ...
Column F = invoice company name
Column AC = alternate company if company starts with Gull
Column AE = Comp model
column AF = Comp serial number
Data! database worksheet
Data!A column = Active record flag
Data!M colum = Comp database serial number
Data!C column = Company name  if the company starts with Gull use a different column "AC" for the name
Data!L column = comp model no
Range("AH3").Formula = "=COUNTIF(C:C,$C3)"
'AI3 = ARRAYED =IF($AF3"",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0)),"Serial Match Error",SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$3 00=$AF3))/100)),"No Serial")
Range("AI3").FormulaArray = "=IF($AF3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0))," & Chr(34) & "Serial Match Error" & Chr(34) & ",SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$3 00=$AF3))/100))," & Chr(34) & "No Serial" & Chr(34) & ")"
'AJ3= Arrayed =IF($AF3"",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0)),"Serial Match Error",IF(ISNA(MATCH(IF(LEFT($F3,4)="Gull",$AC3,$F3),Data!$C$2:$C$300,0)),"Co Match Error",SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)/100)))),"No Serial")
Range("AJ3").FormulaArray = "=IF($AF3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0))," & Chr(34) & "Serial Match Error" & Chr(34) & ",IF(ISNA(MATCH(IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3),Data!$C$2:$C$300,0))," & Chr(34) & "Co Match Error" & Chr(34) & ",SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)/100))))," & Chr(34) & "No Serial" & Chr(34) & ")"
'AK3 = Arrayed =IF($AE3"",IF(ISNA(MATCH(IF(LEFT($F3,4)="Gull",$AC3,$F3),Data!$C$2:$C$300,0)),"Co Match Error",SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4) ="Gull",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100)),"No Model")
Range("AK3").FormulaArray = "=IF($AE3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH(IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3),Data!$C$2:$C$300,0))," & Chr(34) & "Co Match Error" & Chr(34) & ",SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4) =" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100))," & Chr(34) & "N" & Chr(34) & ")"
'AL3 = Arrayed =IF($AE3"",IF(ISNA(MATCH(IF(LEFT($F3,4)="Gull",$AC3,$F3),Data!$C$2:$C$300,0)),"Co Match Error",SUM((Data!$C$2:$C$300=$F3)*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$30 0=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100)),"No Model")
Range("AL3").FormulaArray = "=IF($AE3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH(IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3),Data!$C$2:$C$300,0))," & Chr(34) & "Co Match Error" & Chr(34) & ",SUM((Data!$C$2:$C$300=$F3)*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$30 0=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100))," & Chr(34) & "No Model" & Chr(34) & ")"
I have developed the formula in excel and then i have copied it in the macro and made the appropriate changes (ie " = chr(34) )
The formula error in for cell AJ3
the other formulas prior work.
The length of the formula is approx 360 characters, which i thought was a problem but the formula itself working in excel, except when i try to use VB to embed it in a cell.
The comments prior to the formula Array are the actually formulas copy from the excel worksheet used to write the formula array in VB
In simple terms the formula as checking :
This sheet is a list of invoices to match against the database and update the invoice history.
These formula basically do a multiple match criteria and return a number xx.nn where xx is the sum of the row numbers that match and xx is the match count /100. I expect only to one match, but the nn is there is see if there is more than one match.
I am relucant to include the workbook because i has some much other crap in it but may need to ...
Column F = invoice company name
Column AC = alternate company if company starts with Gull
Column AE = Comp model
column AF = Comp serial number
Data! database worksheet
Data!A column = Active record flag
Data!M colum = Comp database serial number
Data!C column = Company name  if the company starts with Gull use a different column "AC" for the name
Data!L column = comp model no
Range("AH3").Formula = "=COUNTIF(C:C,$C3)"
'AI3 = ARRAYED =IF($AF3"",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0)),"Serial Match Error",SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$3 00=$AF3))/100)),"No Serial")
Range("AI3").FormulaArray = "=IF($AF3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0))," & Chr(34) & "Serial Match Error" & Chr(34) & ",SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$M$2:$M$3 00=$AF3))/100))," & Chr(34) & "No Serial" & Chr(34) & ")"
'AJ3= Arrayed =IF($AF3"",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0)),"Serial Match Error",IF(ISNA(MATCH(IF(LEFT($F3,4)="Gull",$AC3,$F3),Data!$C$2:$C$300,0)),"Co Match Error",SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)/100)))),"No Serial")
Range("AJ3").FormulaArray = "=IF($AF3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH($AF3,Data!$M$2:$M$300,0))," & Chr(34) & "Serial Match Error" & Chr(34) & ",IF(ISNA(MATCH(IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3),Data!$C$2:$C$300,0))," & Chr(34) & "Co Match Error" & Chr(34) & ",SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$C$2:$C$300=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$M$2:$M$300=$AF3)/100))))," & Chr(34) & "No Serial" & Chr(34) & ")"
'AK3 = Arrayed =IF($AE3"",IF(ISNA(MATCH(IF(LEFT($F3,4)="Gull",$AC3,$F3),Data!$C$2:$C$300,0)),"Co Match Error",SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4) ="Gull",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100)),"No Model")
Range("AK3").FormulaArray = "=IF($AE3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH(IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3),Data!$C$2:$C$300,0))," & Chr(34) & "Co Match Error" & Chr(34) & ",SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$A$2:$A$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$300=IF(LEFT($F3,4) =" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100))," & Chr(34) & "N" & Chr(34) & ")"
'AL3 = Arrayed =IF($AE3"",IF(ISNA(MATCH(IF(LEFT($F3,4)="Gull",$AC3,$F3),Data!$C$2:$C$300,0)),"Co Match Error",SUM((Data!$C$2:$C$300=$F3)*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$30 0=IF(LEFT($F3,4)="Gull",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100)),"No Model")
Range("AL3").FormulaArray = "=IF($AE3" & Chr(34) & Chr(34) & ",IF(ISNA(MATCH(IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3),Data!$C$2:$C$300,0))," & Chr(34) & "Co Match Error" & Chr(34) & ",SUM((Data!$C$2:$C$300=$F3)*(Data!$L$2:$L$300=$AE3)*(ROW(Data!$C$2:$C$300)),SUM((Data!$A$2:$A$300=TRUE)*(Data!$C$2:$C$30 0=IF(LEFT($F3,4)=" & Chr(34) & "Gull" & Chr(34) & ",$AC3,$F3))*(Data!$L$2:$L$300=$AE3))/100))," & Chr(34) & "No Model" & Chr(34) & ")"
Hello I am new here and need help with the following.
I have a column A with a range of text fields which contain last names somewhere in the text(some at beginning of string, some at end of string and some in middle of string). In cell B1, I need to search for a range of names(C1:C4) in A1. If a match is found, then the formula should return the the value of the adjacent cell in column D describing their position on the project.
I can do it using VLookup if all the names are on the left.
=VLOOKUP(LEFT(A1,4),C$1:D$4,2, FALSE)
But it wont work if the text is scattered in the cell.
Thanks,
I have a column A with a range of text fields which contain last names somewhere in the text(some at beginning of string, some at end of string and some in middle of string). In cell B1, I need to search for a range of names(C1:C4) in A1. If a match is found, then the formula should return the the value of the adjacent cell in column D describing their position on the project.
I can do it using VLookup if all the names are on the left.
=VLOOKUP(LEFT(A1,4),C$1:D$4,2, FALSE)
But it wont work if the text is scattered in the cell.
Thanks,
I'm using the MATCH function to return the relative position of the first cell in a range that meets a certain criteria:
{=MATCH(TRUE,(D3:J3)>0,0)}
For example, I have a range of numbers (0,6,12,0,24,36) and would like to know the position of the first cell that meets a certain criteria (e.g. >0, or >18).
I tested this formula on a small range (6 columns); it worked, so I proceeded to implement it for the full dataset (102 columns), but the same array formula using the larger range does not work. I don't know the reason for this.
Edit: I didn't clarify "doesn't work". When using the large range, the MATCH function returns a value of "1", regardless of the contents of the cells in the range. (sorry for the imprecise description; thanks, Richard)
Any ideas?
{=MATCH(TRUE,(D3:J3)>0,0)}
For example, I have a range of numbers (0,6,12,0,24,36) and would like to know the position of the first cell that meets a certain criteria (e.g. >0, or >18).
I tested this formula on a small range (6 columns); it worked, so I proceeded to implement it for the full dataset (102 columns), but the same array formula using the larger range does not work. I don't know the reason for this.
Edit: I didn't clarify "doesn't work". When using the large range, the MATCH function returns a value of "1", regardless of the contents of the cells in the range. (sorry for the imprecise description; thanks, Richard)
Any ideas?
Hello,
I've got a problem that i'm having trouble getting my head around. I think it will require a combination of different functions to achieve, but I may be wrong.
I'm using the OFFSET function to select a range for graphing. However I require the reference cell to be dynamic.
I have a data validation list on one sheet which selects a string of words. I then have a cell on another sheet which is set as equal to the data validation one so that it updates when the text changes. From this I need to use the LEFT function to select the first word. The next function needs to then search for that same word in a specific column and return the cell reference so that it can be used as the reference for the OFFSET function.
I already have the LEFT function sorted:
LEFT($D$2,FIND(" ",$D$2)1)
I just need a way to use the word that it returns to search for the same word in row 6 (which I may change to column B later depending on how the format looks) and then return the cell reference of that word.
I'm pretty sure this is possible with the fuctions in excel, i'm just having problems getting my head around how they need to work together and which ones to use. If anyone can give me some help it would be appreciated
I've got a problem that i'm having trouble getting my head around. I think it will require a combination of different functions to achieve, but I may be wrong.
I'm using the OFFSET function to select a range for graphing. However I require the reference cell to be dynamic.
I have a data validation list on one sheet which selects a string of words. I then have a cell on another sheet which is set as equal to the data validation one so that it updates when the text changes. From this I need to use the LEFT function to select the first word. The next function needs to then search for that same word in a specific column and return the cell reference so that it can be used as the reference for the OFFSET function.
I already have the LEFT function sorted:
LEFT($D$2,FIND(" ",$D$2)1)
I just need a way to use the word that it returns to search for the same word in row 6 (which I may change to column B later depending on how the format looks) and then return the cell reference of that word.
I'm pretty sure this is possible with the fuctions in excel, i'm just having problems getting my head around how they need to work together and which ones to use. If anyone can give me some help it would be appreciated
I'm using a number of Index/Match formulas. I've run into the problem where I need to use =Left to extrat part of a value (which can contain numbers and letters) and do an Index/Match off the result.
The Index/Match works when the lookup value contains letters. It does not work if it is only numbers. What is odd is that if I copy and paste special values the cell and look up off of the new cell it won't work, but excel gives me the little "!" icon to the left of the cell. I then tell it to convert to number and it then works.
For example, if my index match is off of 71008 it won't work. If the index match is off of 71008A then it will work. If it is off of 71008 and I multiply by 1 it will work.
So...I feel like I need to force the cell to think that it's a number even if there's text in the cell. Any ideas on how to do this?
Let me know if I'm not being clear.
The Index/Match works when the lookup value contains letters. It does not work if it is only numbers. What is odd is that if I copy and paste special values the cell and look up off of the new cell it won't work, but excel gives me the little "!" icon to the left of the cell. I then tell it to convert to number and it then works.
For example, if my index match is off of 71008 it won't work. If the index match is off of 71008A then it will work. If it is off of 71008 and I multiply by 1 it will work.
So...I feel like I need to force the cell to think that it's a number even if there's text in the cell. Any ideas on how to do this?
Let me know if I'm not being clear.
Can anyone please help me out  probably an easy one for most of you out there.
Im trying to return the position of the first non zero value in a range using the match worksheet function.
Initially I was using the code to return the position of the first "1" in a range when i was only dealing with 0s or 1s.
Now I am dealing with any integer above 0 so im basically looking to return the position of the first non zero value.
So far I have tried
=MATCH(NOT(0),I33:DL33,0)
but it doesnt seem to work.
Thanks in advance
Mike
Im trying to return the position of the first non zero value in a range using the match worksheet function.
Initially I was using the code to return the position of the first "1" in a range when i was only dealing with 0s or 1s.
Now I am dealing with any integer above 0 so im basically looking to return the position of the first non zero value.
So far I have tried
=MATCH(NOT(0),I33:DL33,0)
but it doesnt seem to work.
Thanks in advance
Mike
In another thread I learned something new that doesn't seem evident from the description of the function in Help.
I wouldn't have thought to use an array reference in LEFT but this is legal:
=LEFT($A$1:$A$3,1)
I put this in B1. I would have thought it would just return the first character of A1 (the upper left element), but it appears to return an entire array. If I copy the formula down, I get the first characters of A1, A2, A3, respectively.
TRIM seems to work the same way.
Does anybody know the secret of when a plain vanilla function can work like an array formula without entering it as an array formula? Is there any general rule about this? Or is it just one of those Excel Easter eggs?
I wouldn't have thought to use an array reference in LEFT but this is legal:
=LEFT($A$1:$A$3,1)
I put this in B1. I would have thought it would just return the first character of A1 (the upper left element), but it appears to return an entire array. If I copy the formula down, I get the first characters of A1, A2, A3, respectively.
TRIM seems to work the same way.
Does anybody know the secret of when a plain vanilla function can work like an array formula without entering it as an array formula? Is there any general rule about this? Or is it just one of those Excel Easter eggs?
I know it is possible to use the LEFT and RIGHT function to select text that is a number of digits away from the left or right but is it possible to do this to the first < charachther from the left and the first > from the right.
ie
i have a piece of text like this in a cell A3
TEXT TEXTEXXAMPLELEFT<NL>EXAMPLE TEXT RIGHTTEST TEST
in cell A1 I want everything on the left of the <NL>
in cell B1 I want everything to the right of the <NL>
and i would like to do it without the text import wizard as i need this text to stay in A3 aswell.
thanks
Shane
ie
i have a piece of text like this in a cell A3
TEXT TEXTEXXAMPLELEFT<NL>EXAMPLE TEXT RIGHTTEST TEST
in cell A1 I want everything on the left of the <NL>
in cell B1 I want everything to the right of the <NL>
and i would like to do it without the text import wizard as i need this text to stay in A3 aswell.
thanks
Shane
Is it possible to do what my title says?
I've tried some of the options in the CF drop down but can't get it.
This is the layout:
Sheet1
* E F G H I 2 43/038L $3,210 * 3240 $30 3 34/058S $2,275 * 2617 $342 4 34/029L $327 * 327 $0 5 66/030S $2,684 * #N/A #N/A 6 45/008S $2,791 * 2756 $35 7 41/014S $3,212 * 3212 $0 8 41/014L $2,385 * 2400 $15 9 41/008S $327 * #N/A #N/A
Spreadsheet Formulas Cell Formula H2 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E2,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E2)="S",21040,21038),$A$2:$A$61),0))} I2 =H2F2 H3 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E3,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E3)="S",21040,21038),$A$2:$A$61),0))} I3 =H3F3 H4 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E4,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E4)="S",21040,21038),$A$2:$A$61),0))} I4 =H4F4 H5 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E5,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E5)="S",21040,21038),$A$2:$A$61),0))} I5 =H5F5 H6 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E6,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E6)="S",21040,21038),$A$2:$A$61),0))} I6 =H6F6 H7 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E7,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E7)="S",21040,21038),$A$2:$A$61),0))} I7 =H7F7 H8 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E8,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E8)="S",21040,21038),$A$2:$A$61),0))} I8 =H8F8 H9 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E9,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E9)="S",21040,21038),$A$2:$A$61),0))} I9 =H9F9 Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> Excel Jeanie HTML 4
The greencells in col I are formated greater then 0. Then red less then 0.
Col F is formated if equal to col H.
I am trying to format col E, if col H equals #N/A.
Probaly not possible because H holds a formula??
Thanks
Harry
I've tried some of the options in the CF drop down but can't get it.
This is the layout:
Sheet1
* E F G H I 2 43/038L $3,210 * 3240 $30 3 34/058S $2,275 * 2617 $342 4 34/029L $327 * 327 $0 5 66/030S $2,684 * #N/A #N/A 6 45/008S $2,791 * 2756 $35 7 41/014S $3,212 * 3212 $0 8 41/014L $2,385 * 2400 $15 9 41/008S $327 * #N/A #N/A
Spreadsheet Formulas Cell Formula H2 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E2,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E2)="S",21040,21038),$A$2:$A$61),0))} I2 =H2F2 H3 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E3,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E3)="S",21040,21038),$A$2:$A$61),0))} I3 =H3F3 H4 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E4,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E4)="S",21040,21038),$A$2:$A$61),0))} I4 =H4F4 H5 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E5,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E5)="S",21040,21038),$A$2:$A$61),0))} I5 =H5F5 H6 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E6,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E6)="S",21040,21038),$A$2:$A$61),0))} I6 =H6F6 H7 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E7,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E7)="S",21040,21038),$A$2:$A$61),0))} I7 =H7F7 H8 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E8,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E8)="S",21040,21038),$A$2:$A$61),0))} I8 =H8F8 H9 {=INDEX($C$2:$C$61,MATCH(LEFT(REPLACE(E9,3,1,""),5)+0,IF($B$2:$B$61=IF(RIGHT(E9)="S",21040,21038),$A$2:$A$61),0))} I9 =H9F9 Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> Excel Jeanie HTML 4
The greencells in col I are formated greater then 0. Then red less then 0.
Col F is formated if equal to col H.
I am trying to format col E, if col H equals #N/A.
Probaly not possible because H holds a formula??
Thanks
Harry
This task joins a string together based on a number of characters per cell in the range. I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference.
Col. N contains a color term, it is the second color term in the product description and is recognized by the leading slash "/".
N22 = /Lt.Green
This is showing the isolated portion of the formula:
&LEFT(N22,2)&
The current function finds the first two characters in the string, "/L". When the total string is built, the "/" is removed and I am left with "RO408GBLUESWTL151".
In this case: The following string is built from terms in their corresponding cells, which yields me the item name.
"~P ROME BLUE SNB GRL 08 WHT/LT.GREEN 151"
From this, I build the item number:
yields me this
"RO408GBLUESWTL151"
What I desire is this:
"RO408GBLUESWTG151"
What I am hoping to do is add an IF and a FIND Function.
If the leading character is a slash ,"/", and the third character is a period, ".", then use the slash, "/" , find and use the first character after the period, in this case a "G".
Something like If N22 contains a leading slash and a period, then
"/G"
Here is the entire current formula:
Again, isolating the formula in N is the focus. If I need to use a helper column to achieve this goal, that is fine. I am just hung up on how to find the period, "." the next following character and join back w/ the slash, "/".
=IF(LEN(U22)=5,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,4),
IF(LEN(U22)=4,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,3),
IF(LEN(U22)=3,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,2),
IF(LEN(U22)=2,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,1),
IF(LEN(U22)=1,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&U22,
IF(LEN(U22)=0,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)))))))
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference.
Col. N contains a color term, it is the second color term in the product description and is recognized by the leading slash "/".
N22 = /Lt.Green
This is showing the isolated portion of the formula:
&LEFT(N22,2)&
The current function finds the first two characters in the string, "/L". When the total string is built, the "/" is removed and I am left with "RO408GBLUESWTL151".
In this case: The following string is built from terms in their corresponding cells, which yields me the item name.
"~P ROME BLUE SNB GRL 08 WHT/LT.GREEN 151"
From this, I build the item number:
yields me this
"RO408GBLUESWTL151"
What I desire is this:
"RO408GBLUESWTG151"
What I am hoping to do is add an IF and a FIND Function.
If the leading character is a slash ,"/", and the third character is a period, ".", then use the slash, "/" , find and use the first character after the period, in this case a "G".
Something like If N22 contains a leading slash and a period, then
"/G"
Here is the entire current formula:
Again, isolating the formula in N is the focus. If I need to use a helper column to achieve this goal, that is fine. I am just hung up on how to find the period, "." the next following character and join back w/ the slash, "/".
=IF(LEN(U22)=5,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,4),
IF(LEN(U22)=4,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,3),
IF(LEN(U22)=3,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,2),
IF(LEN(U22)=2,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,1),
IF(LEN(U22)=1,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&U22,
IF(LEN(U22)=0,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)))))))
My ColumnA dates are actually TEXT;
I've seen formulas (Arraytype  Lookup) that IF USED would eliminate my
current need of using the G1:G12 range, but I can't seem to figure it out.
Can someone demonstrate this formula to me?
Sheet1 A B C D E F G 1 SheetName Cdate Jan 2 13Apr09 4/13/2009 Feb 3 20Apr09 4/20/2009 Mar 4 27Apr09 4/27/2009 Apr 5 May 6 Jun 7 Jul 8 Aug 9 Sep 10 Oct 11 Nov 12 Dec Excel 2003
Worksheet Formulas Cell Formula B2 =DATE( "20" & TEXT( RIGHT( A2,2 ),"00" ),INDEX( {1,2,3,4,5,6,7,8,9,10,11,12},MATCH( MID( A2,3,3 ),$G$1:$G$12,0 ) ),LEFT( A2,2 ) ) B3 =DATE( "20" & TEXT( RIGHT( A3,2 ),"00" ),INDEX( {1,2,3,4,5,6,7,8,9,10,11,12},MATCH( MID( A3,3,3 ),$G$1:$G$12,0 ) ),LEFT( A3,2 ) ) B4 =DATE( "20" & TEXT( RIGHT( A4,2 ),"00" ),INDEX( {1,2,3,4,5,6,7,8,9,10,11,12},MATCH( MID( A4,3,3 ),$G$1:$G$12,0 ) ),LEFT( A4,2 ) )
Thanks in ADVANCE for any help !!
Jim
I've seen formulas (Arraytype  Lookup) that IF USED would eliminate my
current need of using the G1:G12 range, but I can't seem to figure it out.
Can someone demonstrate this formula to me?
Sheet1 A B C D E F G 1 SheetName Cdate Jan 2 13Apr09 4/13/2009 Feb 3 20Apr09 4/20/2009 Mar 4 27Apr09 4/27/2009 Apr 5 May 6 Jun 7 Jul 8 Aug 9 Sep 10 Oct 11 Nov 12 Dec Excel 2003
Worksheet Formulas Cell Formula B2 =DATE( "20" & TEXT( RIGHT( A2,2 ),"00" ),INDEX( {1,2,3,4,5,6,7,8,9,10,11,12},MATCH( MID( A2,3,3 ),$G$1:$G$12,0 ) ),LEFT( A2,2 ) ) B3 =DATE( "20" & TEXT( RIGHT( A3,2 ),"00" ),INDEX( {1,2,3,4,5,6,7,8,9,10,11,12},MATCH( MID( A3,3,3 ),$G$1:$G$12,0 ) ),LEFT( A3,2 ) ) B4 =DATE( "20" & TEXT( RIGHT( A4,2 ),"00" ),INDEX( {1,2,3,4,5,6,7,8,9,10,11,12},MATCH( MID( A4,3,3 ),$G$1:$G$12,0 ) ),LEFT( A4,2 ) )
Thanks in ADVANCE for any help !!
Jim
Here is my complete formula: It is 1020 characters including spaces. Once I get to row 10 and down it only displays 564 characters including spaces. It seems to occur once I get to the double digit rows. It seems that my formula still performs ok, but I am a little nervous about this.
Thanks,
Doug
=IF(LEN(U9)=5,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,4),IF(LEN(U9)=4,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,3),IF(LEN(U9)=3,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,2),IF(LEN(U9)=2,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,1),IF(LEN(U9)=1,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&U9, IF(LEN(U9)=0,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2),"FIX"))))))
Thanks,
Doug
=IF(LEN(U9)=5,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,4),IF(LEN(U9)=4,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,3),IF(LEN(U9)=3,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,2),IF(LEN(U9)=2,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&LEFT(U9,1)&RIGHT(U9,1),IF(LEN(U9)=1,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2)&U9, IF(LEN(U9)=0,F9&LEFT(Q9,1)&LEFT(I9,2)&MID(I9,FIND(" ",I9)+1,2)&LEFT(J9,1)&LEFT(K9,1)&LEFT(M9,1)&RIGHT(M9,1)&LEFT(N9,2)&LEFT(O9,6)&LEFT(R9,2)&LEFT(P9,2),"FIX"))))))
I have an excel problem. I need to track training event currency for a training program. Table looks like this:
1Jan 2Jan 3Jan 4Jan
Jane p o l
Andy p o p l
Joe o p l o
The pol are different training programs. I need a table that will return the last date the person did the training. I wrote a indexmatch function that does this very well. The problem is that when I make the value dicrete by putting a "0" at the end of the MATCH function excel searches the table from left to right and stops at the first occurance of the date. So for training "p" it will return 1Jan when it should return 3Jan for Andy. How do I get it to look right to left?
1Jan 2Jan 3Jan 4Jan
Jane p o l
Andy p o p l
Joe o p l o
The pol are different training programs. I need a table that will return the last date the person did the training. I wrote a indexmatch function that does this very well. The problem is that when I make the value dicrete by putting a "0" at the end of the MATCH function excel searches the table from left to right and stops at the first occurance of the date. So for training "p" it will return 1Jan when it should return 3Jan for Andy. How do I get it to look right to left?
Hi
I am having problems with returning a result for the match function. I am able to return a result using the vlookup function (using the same lookup criteria and range), so the integrity of my search criteria I think is OK.
My goal is to ultimate goal is to determine the ComboBox4.RowSource but I was first trying to simplify the code by getting a value using ComboBox4.value
1. I am trying to match the product from the result of ComboBox2. The match (will always be a match) will be in the sheet named "Variables" in column B.
2. The range (horizontal) of the RowSource will then be starting in the same sheet (Variables), starting in the same row in Column E. (It will always start in Column E, so the row number of the match is required The range may sometimes be only Column E or alternatively it could be many columns, so was going to use the "End(xlToRight)" once I could get a cell reference for the first cell address in the range!
3. Was trying vlookup / match (to get row number) / address / indirect  to no avail
Any help would be greatly appreciated.
Thanks
Russel
VB:
I have subsequently managed to use a vlookup to a cell which contains the range. The lookup value would be something like $E$12:$N$12. When the control source picks this up, it is only recognizes the RowSource as being $E$12. Does anyone have any idea why this is. The code for this vlookup is below:
VB:
I am having problems with returning a result for the match function. I am able to return a result using the vlookup function (using the same lookup criteria and range), so the integrity of my search criteria I think is OK.
My goal is to ultimate goal is to determine the ComboBox4.RowSource but I was first trying to simplify the code by getting a value using ComboBox4.value
1. I am trying to match the product from the result of ComboBox2. The match (will always be a match) will be in the sheet named "Variables" in column B.
2. The range (horizontal) of the RowSource will then be starting in the same sheet (Variables), starting in the same row in Column E. (It will always start in Column E, so the row number of the match is required The range may sometimes be only Column E or alternatively it could be many columns, so was going to use the "End(xlToRight)" once I could get a cell reference for the first cell address in the range!
3. Was trying vlookup / match (to get row number) / address / indirect  to no avail
Any help would be greatly appreciated.
Thanks
Russel
VB:
Private Sub ComboBox2_Change() 'ComboBox4.Value = WorksheetFunction.Match(ComboBox2.Value & "SPH", Range("ProdVariable"), 0) ' THIS CODE DOES NOT RETURN ANY VALUE 'ComboBox4.Value = WorksheetFunction.Match(ComboBox2.Value, Range("ProdVariable"), 0) ' I TRIED SIMPLIFYING THE ABOVE CODE BY REMOVING THE & "SPH". THIS TOO DID NOT RETURN A VALUE 'ComboBox4.Value = WorksheetFunction.VLookup(ComboBox2.Value & "SPH", Range("ProdVariable"), 4, False) 'THIS CODE WORKS(RETURNS FIRST VALUE OF DESIRED RANGE  The lookup Range starts in Column B, and the "4" returns the value in Column E, which is the first value of the range). I ultimately require the Range for the RowSource, not the value! End SubIf you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I have subsequently managed to use a vlookup to a cell which contains the range. The lookup value would be something like $E$12:$N$12. When the control source picks this up, it is only recognizes the RowSource as being $E$12. Does anyone have any idea why this is. The code for this vlookup is below:
VB:
ComboBox3.RowSource = "Variables!" & WorksheetFunction.VLookup(ComboBox2.Value & "SPH", Range("ProdVariable"), 3, False)If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I would like to use the MATCH function to return the value in a range that
meets a certain condition. For example, I would like it to look at the sum
of two cells across a number of columns and when the sum of those cells is
greater than 100 than return the position of that cell in the array. For
example, I would like to add the values of rows 10 and 15 across columns b 
f and when that sum is greater than 100 I would like the formula to return
the first position in the range for which that is true. So if the sum of c10
+ c15=105 the formula would return "2" since it is the second column in the
range that begins with "b". Thank you for your assistance.
meets a certain condition. For example, I would like it to look at the sum
of two cells across a number of columns and when the sum of those cells is
greater than 100 than return the position of that cell in the array. For
example, I would like to add the values of rows 10 and 15 across columns b 
f and when that sum is greater than 100 I would like the formula to return
the first position in the range for which that is true. So if the sum of c10
+ c15=105 the formula would return "2" since it is the second column in the
range that begins with "b". Thank you for your assistance.
Hello, I am looking for suggestions. I have Excel 2007 and am trying to get conditional formatting to work.
Here is the basic concept:
Conditional formatting with formula =AND(D6=D$33)
D6 = 95%
D32 = 100%
D33 = 90%  but is from a text formula, =left("90%99%",3)
I think the problem is in D33 because the "90%99%" value is text, and left() is a text/string function. so I SEE 90% in D33 (and Excel's format is percentage), but it's really still text (ISNUMBER returns false, if i type "90%" then it returns true)
I tried =text(D33,"0%"), but I think that just formats for viewing and still keeps it as text.
Is there a way I can get around this without VBA or anything too complicated?
thanks!
Here is the basic concept:
Conditional formatting with formula =AND(D6=D$33)
D6 = 95%
D32 = 100%
D33 = 90%  but is from a text formula, =left("90%99%",3)
I think the problem is in D33 because the "90%99%" value is text, and left() is a text/string function. so I SEE 90% in D33 (and Excel's format is percentage), but it's really still text (ISNUMBER returns false, if i type "90%" then it returns true)
I tried =text(D33,"0%"), but I think that just formats for viewing and still keeps it as text.
Is there a way I can get around this without VBA or anything too complicated?
thanks!
Hi guys,
I've been an excel lurker for many months. I need some help regarding the match function and returning a text string.
Question:
How do I compare 2 separate worksheets with 1 common column to match then if match, return also a text string from a different column in worksheet2. I already understand how to return a match but don't know how to populate the next cell with the text string from worksheet2.
Many thanks.
I've been an excel lurker for many months. I need some help regarding the match function and returning a text string.
Question:
How do I compare 2 separate worksheets with 1 common column to match then if match, return also a text string from a different column in worksheet2. I already understand how to return a match but don't know how to populate the next cell with the text string from worksheet2.
Many thanks.
Dear All,
I have a column of text where I need to remove all the characters to the right of the last occurance of a special character.
I think a process like reading from right to left, look for the first occurance of the special character, and return the characters to the left of this position.
If I can determine the position of the last occurance of the special character, I could use the LEFT function.
The SEARCH function is close. It finds the position of the first occurance of text inside text but it reads from left to right. I need to read from right to left.
Another approach is to examine each character one by one from right to left. If the character is not the special character, delete it. When the character is the special character, delete it and stop the process.
There is no consistency in the text. The total lengths vary. The number of times the special character occurs in the text vary. The number of characters to the right or left of the last special character vary.
I much prefer not to have the solution be some VBA because I need to share it with others who are even less capable than I am. We are using Excel 2003.
Thanks for your help,
GL
I have a column of text where I need to remove all the characters to the right of the last occurance of a special character.
I think a process like reading from right to left, look for the first occurance of the special character, and return the characters to the left of this position.
If I can determine the position of the last occurance of the special character, I could use the LEFT function.
The SEARCH function is close. It finds the position of the first occurance of text inside text but it reads from left to right. I need to read from right to left.
Another approach is to examine each character one by one from right to left. If the character is not the special character, delete it. When the character is the special character, delete it and stop the process.
There is no consistency in the text. The total lengths vary. The number of times the special character occurs in the text vary. The number of characters to the right or left of the last special character vary.
I much prefer not to have the solution be some VBA because I need to share it with others who are even less capable than I am. We are using Excel 2003.
Thanks for your help,
GL