Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Using Left To Return Match

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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 re-formatting 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

View Answers     

Similar Excel Tutorials

Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...
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 ...
Best Lookup Formula in Excel - Index and Match
A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Match lookups offer you free ...
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 ...

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
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
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
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
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

Similar Topics







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 ...


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:

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!


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.


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) & ")"


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,


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 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




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" sub-string - 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

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)))))))


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 p-o-l are different training programs. I need a table that will return the last date the person did the training. I wrote a index-match 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:

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 Sub 


If 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




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


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.


Afternoon all,

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


Hello,

I have a range of data that I would like to conditionally format all cells that have an error to have white font. Unfortunately, I cannot use a function like this:

if(iserror(function,"",function))

because the formulas in this range are already too long to be able to duplicate for the 'value if false' argument. I have also tried using the conditional formatting tool by setting "Formula Is" equal to:
=ISERROR($A$1)
but this only whites out errors in cell A1 or column A (depending on what I selected.

I have pasted the formula below that is giving me the error if anyone has any ideas on how to modify the formula as well. It is basically an index match formula that is looking at several different data points on another worksheet and averaging them. Thanks in advance.

=AVERAGE(INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$2,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH( $C18,SRD!$A$4:$A$351,0),MATCH(M$3,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$4,SRD! $D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$5,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351, MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$6,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$ 7,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$13,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$ DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$8,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),M ATCH(M$9,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$10,SRD!$D$2:$DG$2,0)),INDEX(SRD !$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$11,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$ 351,0),MATCH(M$12,SRD!$D$2:$DG$2,0)))


I use the Small if function a lot for returning row references. It allows me to find the 2nd, 3rd match and so on by changing the K in the formula.
Instead of copying the formula down, I would like to highlight the range and return multiple results for the range.
Here is the formula for returning a singe result and copying down.
Code:

small(if(A1:A4=C1,ROW(A1:A4)),1)


Here it is for multiple results using the match formula.
Excel Jeanie HTMLSheet1

A B C D 1 D A 4 2 C B 3 3 B C 2 4 A D 1
Spreadsheet Formulas Cell Formula D1 {=MATCH(C1:C4,A1:A4,0)} Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> Excel Jeanie HTML 4
Is there a way to do this with the small if function returning the same results as the match formula?


Okay so I'm trying to do a logical test and if the results is true I want the cell to return a "yes", if its false I want it to return "". I tried the following:

in cell C2:
=IF(B2='Sheet2'!B:B,"Yes","")

however for some reason this was missing A LOT of items that should've been returned as true. So I tried another approach

In cell D2:
=MATCH(B2,'Sheet2'!B:B,0)
In Cell C2
=IF(D2>0,"Yes","")

This finds all the items that the plain "IF" function was missing, however, if there was no match in cell D2 it returns #N/A, which overrides the IF function in cell C2.

So lastly I tried:

=IF(Match(B2,'Sheet2'!B:B,0),"Yes","")

which is still returning the #N/A error.

Any ideas?? Any help would be GREAT! Thanks!!


Hello, I need a little help in formatting a formula for Adding Values.

I have Cells:
G10, H10, I10
The value in these cells are numbers and a Letter 'D' or 'N' (for day or Night)
I Wanted to Add the values so I made this formula

=LEFT(G10,LEN(G10)-1)+LEFT(H10,LEN(H10)-1)+LEFT(I10,LEN(I10)-1)& " hrs"

This works perfect, BUT if I need to leave one of the cell Blank, when there is no Value to enter. Then comes the error of #Value!
I need to sort it out that if the Cell is left blank Excel should consider the value of blank cell as 0.

Also I want to know what If I want to add the complete row, for example for G10 to V10
(like we do =sum(g10:v10) But I need to have same as above with Left and Len functions.

Im also attaching the Spread sheet where I did this.

Thanks, Waiting for prompt Help

I have been trying a match formula in order to get returned in column B the position in column A of a certain value. but I cant make it work properly. I would like that the returned matches appears one after the other in the correct order, even better and more elegant would be to be able to ask for the match position i.e display only third match.


A B C D 1 LOOKUP COULUMN RETURN LOOKUP VALUE 2 1 First match 3 2 3 2 Send match 9 4 3 Third match 11 5 4 Fourth match 19 6 5

7 4

8 3

9 2

10 1

11 2

12 3

13 4

14 5

15 6

16 5

17 4

18 3

19 2

20 1


I am using Excel 2002 and trying to do something which should be simple with MATCH.

I have an array of numbers ordered, starting at 0 and incrementing by 0.05 up to 20. I am then using the MATCH function to find the relative position of numbers 0,1,2,3,4 and 5. However, for number 0,1 and 2 the return value is one less than what it should be, for the numbers 3, 4 and 5 it is working as expected. By the way, the match_type I am using is the default 1.

Also I have formatted everything to be numbers and tried various things already, so am beginning to wonder is this is a known issue. I really hope not.


Here is a formula which I am using to identify if a date is within a financial year from a date stored as text with varying formats and lengths.

A B C D 1 YEAR DATE AS TEXT RETURNS FORMULA USES THE YEAR AND THE DATE WHICH IS STORED AS TEXT => SHOULD RETURN TRUE OR FALSE 2 2010 1/09/2010 13:47:18.867 #VALUE! =OR(AND(1*RIGHT(LEFT(C2,10),4)=B2,1*RIGHT(LEFT(C2,5),2)>6),AND(1*RIGHT(LEFT(C2,10),4)=B2+1,1*RIGHT(LEFT(C2,5),2) #VALUE! =OR(AND(1*RIGHT(LEFT(C3,10),4)=B3,1*RIGHT(LEFT(C3,5),2)>6),AND(1*RIGHT(LEFT(C3,10),4)=B3+1,1*RIGHT(LEFT(C3,5),2) #VALUE! =OR(AND(1*RIGHT(LEFT(C4,10),4)=B4,1*RIGHT(LEFT(C4,5),2)>6),AND(1*RIGHT(LEFT(C4,10),4)=B4+1,1*RIGHT(LEFT(C4,5),2) TRUE =OR(AND(1*RIGHT(LEFT(C5,10),4)=B5,1*RIGHT(LEFT(C5,5),2)>6),AND(1*RIGHT(LEFT(C5,10),4)=B5+1,1*RIGHT(LEFT(C5,5),2) FALSE =OR(AND(1*RIGHT(LEFT(C6,10),4)=B6,1*RIGHT(LEFT(C6,5),2)>6),AND(1*RIGHT(LEFT(C6,10),4)=B6+1,1*RIGHT(LEFT(C6,5),2)

I almost have my program running. I'm getting errors while using the match function, however.
This is the syntax I am using to try to set the value of the first row as the active cell:

Excel.WorksheetFunction.Match(i, Worksheets(1).Range("A6:A55"), 0).Select

"i" is the variable that I stored t/r as, (t and r are user inputs). I have pretty much the exact same thing for my second look up only I set the activecell as an offset (0,1) and then made the range ("activecell:b55").

When I use this function without the match part of it and only use the offsets to return table values it works fine, I just need to know how to debug the match function so that it will set the found value as the active cell?

I have am now aware that the match function only returns a row number and not a value, however is there a way to make the value the match function finds the active cell? That way I can just use offsets to return the data from my table that I actually need.


OK so this is a problem that has annoyed me for quite some time now. I use the Match function at work a lot to compare worksheets, cross-check things, etc. It fails me, however, when I am trying to match a number, for example, in another column when that other column is numbers stored as text. For example, if column A has "545564" stored as a number and column B has "545564" stored as text, Match will return "NA" when looking for that number in either column!

I have also noticed that highlighting the entire column and setting the data type to Number does not get rid of the "Number stored as text" error in each cell and does not fix the Match problem. Right now I use the Value function to make a new column of numbers from the text column and compare using that. Or, if I am comparing the other way, I just have to use the Value function on the look-up value.

I did write my own function to compare each cell in a range to a look-up value by looping through the range and testing for equality but it is way slower than Match and using it for thousands of cells dramatically slows down opening and closing the spreadsheet, etc.

Anyone have a better way to test if a value is present in a range? Just in writing this I am thinking of using Application.Find... Any help is greatly appreciated and thanks in advance!


Hi y'all, this shouldn't be too hard but it has caused me some problems in the past and I can't find a solution.

I want to use the above mentioned functions to return a number value and not text. I would like to extract for example the first 3 nubers from 200-102 but using LEFT returns a text value. I thought there was an easy way to convert the answer into a number - similar to the TEXT function. But I can't find it...

Please help! Thanks.


In A1 I want to have a formula that strips out just the 2nd word from a text string in B1. I have a formula that works, I just wanted to know if anyone can think of a more simple way to solve the problem. The Text string is: "Document 100181072 1001 was parked", and I want the formula to return the number "100181072" no matter what the first word says, and no matter how long the number is.

The idea behind the formula I have that works is, determine the position of the first two spaces in the text string, then carve out the 2nd word using the left and right functions. Can anyone think of a simpler or more elegant way to pull out a particular word from a text string?

This is the formula that I have now:

=RIGHT(LEFT(B1,FIND(" ",B1,(FIND(" ",B1,1))+1)-1),(FIND(" ",B1,(FIND(" ",B1,1))+1)-FIND(" ",B1,1))-1)