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 Tutorials
How to use the Vlookup Function in Excel
Full explanation of the Vlookup function in Excel, what it is, how to use it, and when you should use it. The Vlook ...
Full explanation of the Vlookup function in Excel, what it is, how to use it, and when you should use it. The Vlook ...
HLOOKUP in Excel
The Hlookup function allows you to scan a row from left to right in search of a value and then return the contents ...
The Hlookup function allows you to scan a row from left to right in search of a value and then return the contents ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
Quickly Replace A Lot of Data in Excel
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
Helpful Excel Macros
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
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
Vlookup Macro to Return All Matching Results from a Sheet in Excel
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
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
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".
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) & ")"
The current vlookup formula below doesn't return new information just populates if there is a match. Thinking maybe I need a match formula. However, there is a second part to the formula I need.
=IF(ISERROR(VLOOKUP(LEFT(E3,FIND(" ",E3)1),List!E:E,1,0)),"",(VLOOKUP(LEFT(E3,FIND(" ",E3)1),List!E:E,1,FALSE)))
There is a second step to the formula I need is If the Vlookup returns a result, then I need to see if the manufacturers match.
If column T on the master tab returned a result, I need it to look at the list tab (Manufacturer name) and compare that with MFG on the master tab (for that same line that returned the result). The key is that Only part of the Manufacturer name needs to match. For example, Duraline Arco on the master tab, matches with Arnco on the list tab.
thanks
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?
I am using the match function to look up a list of names in a tab on my spreadsheet. Next I want to return the project number in the same row, but 15 columns to the left in another tab in my workbook. The relative reference part is not working. If I ask the a number is put in the cell it works just fine, but I want to use the value to the far left of the same row.
=IF(ISNUMBER(MATCH("*"&C4&"*",MPL!Q1:Q32,0)),RC[15],"")
Thank you,
Robin
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 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
Hi,
I have a column of data  containing text strings.
The text strings are very similar (start and middle of strings are identical) but are unique due to characters at the end of each string.
I would like to truncate the text strings  by finding the last "common" substring  which I specify  and use LEFT to return the remainder.
The strings that I want to search for are
1) "_reg_"
2) "["
3) "shiftflop"
=IFERROR(LEFT(C2,FIND("_reg_",C2)1),IFERROR(LEFT(C2,FIND("[",C2)1),IFERROR(LEFT(C2,FIND("shiftflop",C2)1),C2)))
This works for the first 2 cases  but NOT the last.
If I remove the first 2 and ONLY include the last it works fine!
=IFERROR(LEFT(C2,FIND("shiftflop",C2)1),C2)))
Is there an error in the formula or a limitation when "nesting" like that ?
Is there any issue including the char "["
I also tried using search function:
=IFERROR(LEFT(A1,SEARCH({"[","_reg_","shiftflop"},A1)1),A1)
I believe that SEARCH({"A","B","C"},A1)1) is equivalent of
search for any of the letters A,B OR C in cell A1 and return the cell position for the fist match you find.
Is that correct?
Again this worked for some cases but not all.
I am not sure what the correct syntax is  so looking for some help.
Thanks
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"))))))
Hi All,
First time poster so forgive any unintentional forum rudeness. I'm using INDEX MATCH MATCH with OFFSET to find a value, then find a text string on the row of that value, and return the contents of the cell to the right of the cell containing the text. The text string is a value in the data set, is not in the column header, and it may be in any one of five different columns. INDEX MATCH MATCH works as long as I plug the specific row into the second MATCH criteria, but I can't figure out a way to specify the correct row to locate the text string on that row. On the plus side, I will always be looking for the same text string (i.e. "AUX")
The workbook sample may illustrate this better than my description. Index statement is in cell B14.
Example: For item A5678 (A7), I want to find the Position ID (G7) for the AUX position (Column F, but could be in columns B, D, F, H, or J). I get a valid result if I add the row number to the second match:
Please Login or Register to view this content.
How do I indicate the row number dynamically for all rows?
Thanks in advance for any help.
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
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!
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
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.
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.
Afternoon all,
I was wondering if anyone could help me with the following formula:
Code:
I don't seem to be getting the desired result, the value returned is #N/A when it should be 4%.
Thanks
I was wondering if anyone could help me with the following formula:
Code:
=INDEX(Calc,MATCH(RIGHT(U11,4),Cost_Centre,0),MATCH(LEFT(U11,2),Trade_Class,0))/12*1*I11
I don't seem to be getting the desired result, the value returned is #N/A when it should be 4%.
Thanks